Finding date and time for max and min values

[Pat]

How can you find the associated date and time of a max or min value of a
column of numbers?

A B C
1 1/1/08 12:00:00 25 min 13 @ 1/1/08 12:10:00
2 1/1/08 12:05:00 34 max 34 @ 1/1/08 12:05:00
3 1/1/08 12:10:00 13

 

[Bob Phillips]

="Max "&MAX(C1:C3)&" @ "&TEXT(INDEX(A1:A3,MATCH(MAX(C1:C3),C1:C3,0)),"d/m/yy
")&TEXT(INDEX(B1:B3,MATCH(MAX(C1:C3),C1:C3,0)),"hh:mm:ss")

etc.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Ron Coderre]

Try these (in sections to avoid text wrap issues):

For the minimum:
="min "&MIN(C2:C4)&" @ "&TEXT(INDEX(A2:A4+B2:B4,MATCH(
MIN(C2:C4),C2:C4,0)),"d/m/yy hh:mm:ss")

For the maximum:
="max "&MAX(C2:C4)&" @ "&TEXT(INDEX(A2:A4+B2:B4,MATCH(
MAX(C2:C4),C2:C4,0)),"d/m/yy hh:mm:ss")

Is that something you can work with?
Post back if you have more questions.
--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)