Find a cell(date) that corresponds to a particular maximum value

  • Thread starter Thread starter Pats
  • Start date Start date
P

Pats

Hi there,
I've got some temperature values on B5:I34 and I used the MAX functio
to find the maximum temperature in that range. The maximum value is a
cell F5. The date column is on cells A5:A34. My question is how can
retrieve the date corresponding to that particular maximum value
similar to minimum value?

Appreciate any help.
:confused: Pats
 
One way:

Assuming *no ties* in the max and min temperatures in B5:I34

Using 2 empty cols, say cols J and K

Put in J5: =MAX(B5:I5)
Put in K5: =MIN(B5:I5)
Select J5:K5 and copy down to K34

Put in J4:
=OFFSET($A$5,MATCH(MAX($J$5:$J$34),$J$5:$J$34,0)-1,)

Put in K4:
=OFFSET($A$5,MATCH(MIN($K$5:$K$34),$K$5:$K$34,0)-1,)

Format J4:K4 as dates

J4 returns the date for the max temp
K4 returns the date for the min temp
 
This worked for me:

Maximum:
=INDEX(A5:A34,1-ROW(B5)+MIN(IF(B5:I34=MAX(B5:I34),ROW(B5:I34))))

Minimum:
=INDEX(A5:A34,1-ROW(B5)+MIN(IF(B5:I34=MIN(B5:I34),ROW(B5:I34))))

Both of these are array formulas. Hit ctrl-shift-enter instead of enter.
If you do it correctly, excel will wrap curly brackets {} around your formula.
(don't type them yourself.)

If there are ties, you'll see the value that's on the lower numbered row.
 
Dave Peterson said:
Based on a Harlan Grove response to a different question.
(he's very smart.)
Click to expand...

ahh .. but of course .. its a "known" <bg>
but you're as modest as ever, Dave ..

heck, I'm sure glad to have experienced folks like you, Harlan,
and the many other regulars hanging around these excel ngs !
 
Back
Top