Max Value - based on a time period

[Chris26]

Hi

I have a number of data vaules (Col-B) per day for a number of years. Is
there a formula that I could use to extract the MAX value of Col-B for say,
Year 2000, 2001, 2002 etc. I have a number of series of data, and each series
has approx 10,000 lines of data hence be easier if use formula.
The frequency of the data values, varies, there could be 10 on one day, 5
the next etc.
I have previously used SUMPRODUCT to get the sumation of similar data but
how do I get the MAX value. Any ideas?

The Data is as follows.

Col-A Col-B

01/01/2000 09:10:18 0.005
01/01/2000 14:22:08 0.008
01/01/2000 23:14:28 0.004

02/01/2000 03:08:36 0.011
02/01/2000 11:10:40 0.018

31/12/2008 09:10:18 0.009


Many Thanks
Chris

 

[Jacob Skaria]

Try the below. 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(A2:A100,"yyyy")="2009",B2:B100))

If this post helps click Yes

 

[Chris26]

This worked fine, many thanks
Chris

Try the below. 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(A2:A100,"yyyy")="2009",B2:B100))

If this post helps click Yes

 

[JoeU2004]

I have a number of data vaules (Col-B) per day for a number of years. Is
there a formula that I could use to extract the MAX value of Col-B for
say,
Year 2000, 2001, 2002 etc.

If you simply want the max of column B for a specified year in column A, try
the following array formula (commit with ctrl-shift-Enter, not just Enter):

=max(if(year($A$1:$A$10000)=C1,$B$1:$B$10000))

where C1 contains the year. I have purposely mixed absolute and relative
references so that you can copy the formula down a column parallel to, say,
C1:C9 that contains the years 2000 through 2009.


However....

The frequency of the data values, varies, there could be 10 on one day,
5 the next etc.

I do not understand the relevancy of that comment. If you are saying that
you want the sum of all instances of the max value in a specified year, try
the following array formula (again, commit with ctrl-shift-Enter, not just
Enter):

=sumproduct((year($A$1:$A$10000)=C1)*($B$1:$B$10000=max(if(year($A$1:$A$10000)=C1,$B$1:$B$10000))),$B$1:$B$10000)


----- original message -----