maximum value (for a calendar date)

[Chris26]

I have the following data in my spreadsheet.

Col-A Col-B
(Depth)
150 20/09/2008 00:00
155 20/09/2008 00:02
157 20/09/2008 00:04
......
350 15/04/2009 22:46
etc.

I need to extract into a new column/table the maximum value of Col A per day.
I have a number of spreadsheets of this data hence rather have a formula
than do manually.

Any help appreciated
Many Thanks Chris

 

[Jacob Skaria]

With your dates in Col C starting from cell C1; try the below formula in D1
and copy down as required....

Please note that this is an array formula. Within the cell in edit mode (F2)
paste this formula and press Ctrl+Shift+Enter to apply this formula. If
successful in 'Formula Bar' you can notice the curly braces at both ends like
"{=<formula>}"

=MAX(IF(TEXT(B1:B100,"ddmmyyyy")=TEXT(C1,"ddmmyyyy"),A1:A100))

 

[Chris26]

Tha worked fine, Thankyou.

Also trying out sumproduct formula and managed to get that to work as well.
=SUMPRODUCT(MAX((C2:C100=D2)*A2:A100))

Where Col C is the date/time formatted to just date and D is date column in
table.

Many thanks

 

[T. Valko]

=MAX(IF(TEXT(B1:B100,"ddmmyyyy")=TEXT(C1,"ddmmyyyy"),A1:A100))

If C1 is a date only and B:B are dates + times:

=MAX(IF(INT(B1:B100)=C1,A1:A100))