Finding Time that Max Occured

[Guest]

The following table represents times (first two rows) and amounts (next 2
rows). I am trying to find the time that the max amount occured (ex. 201
occured first at 1:15). I am trying to determine by using a formula to the
right of the data table. What formula could I use to make this happen?

Date/Time 0:15 0:30 0:45 1:00 1:15 1:30 1:45 2:00 2:15 2:30 2:45 3:00
1/1/2005 196 198 196 199 201 201 199 143 82 70 62 65

 

[Guest]

Try something like this:

With your data in cells A1:M2

N2: =LOOKUP(MAX(B2:M2),B2:M2,$B$1:$M$1)
(formatted as Time)

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP

 

[Franz Verga]

The following table represents times (first two rows) and amounts
(next 2 rows). I am trying to find the time that the max amount
occured (ex. 201 occured first at 1:15). I am trying to determine by
using a formula to the right of the data table. What formula could I
use to make this happen?

Date/Time 0:15 0:30 0:45 1:00 1:15 1:30 1:45 2:00 2:15 2:30 2:45 3:00
1/1/2005 196 198 196 199 201 201 199 143 82 70 62 65

Hi RV,

try with this:

=INDEX($B$1:$M$2,1,MATCH(MAX($B$2:$M$2),$B$2:$M$2,0))

you have to format as Time the cell in which you insert the formula (menu
Format, Cells, tab Numbers, category Time).

--
Hope I helped you.

Thanks in advance for your feedback.

Ciao

Franz Verga from Italy

 

[Guest]

Actually, the formula I posted has some issues in certain circumstances...

This one overcomes them. It returns the time of the first instance of the
maximum:
N2: =INDEX($B$1:$M$1,1,MATCH(MAX(B2:M2),B2:M2,0))

Does that help?
***********
Regards,
Ron

XL2002, WinXP