How to find the time that a max value occurs?

**tom.bateman** · 08-26-2012, 09:32 PM

Hi,

I have a spreadsheet that has data taken for every 10 minutes throughout the day for close to a year. Im having trouble trying to find the time that the max value occurs in my data for each day.
Ive got a column with the times that the data is recorded, a column that has just the date (in number format), a column of the data and a column that finds the max value for that particular day using the following formula:

=IF(B2<>B3,MAX(($B$2:$B$1048576=B2)*($C$2:$C$1048576)),"")

Ive tried using the formula below to find the time of day, however there are a number of duplicates on different days in the data so it only returns the earliest date that it finds.

=IF(B2<>B3,INDEX(A:A,(MATCH(E2,C:C,0))),"")

Can anyone please help me with a formula to find the time the max value occurs each day. I have attached a spreadsheet that is similar to the one I'm working on which may help.

Thanks

**martindwilson** · 08-27-2012, 06:12 AM

use a helper put this array formula in d2 fill down
=IF(C2=MAX(IF($B$2:$B$2000=B2,$C$2:$C$2000,0)),ROW(),"")
see here on how to enter arrays
http://office.microsoft.com/en-us/ex...001087290.aspx
then max flow in e2=IF(ROW(A1)>COUNT($D$2:$D$2000),"",INDEX($C$2:$C$2000,MATCH(SMALL($D$2:$D$2000,ROW(A1)),$D$2:$D$2000,0)))
and date time in f2 =IF(ROW(a1)>COUNT($D$2:$D$2000),"",INDEX($A$2:$A$2000,MATCH(SMALL($D$2:$D$2000,ROW(a1)),$D$2:$D$2000,0))