maximum value (for a calendar date)
 

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

maximum value (for a calendar date)
 

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))
--
If this post helps click Yes
---------------
Jacob Skaria


"Chris26" wrote:

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

maximum value (for a calendar date)
 

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


"Jacob Skaria" wrote:

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))
--
If this post helps click Yes
---------------
Jacob Skaria


"Chris26" wrote:

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

maximum value (for a calendar date)
 

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

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

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

--
Biff
Microsoft Excel MVP


"Jacob Skaria" wrote in message
...
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))
--
If this post helps click Yes
---------------
Jacob Skaria


"Chris26" wrote:

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