using dmax

D

dbasmb

I want to find the max temperature for each day of the year over 10 years
worth of data. I can do it by using a criteria where the first in the range
of the criteria is the label ("Date" in this case) and under that cell is the
date I want to look up (eg "Jan,3"). But it seems like I need to have 2
cells per day of the year in order to specify each day. That is, it looks
like I have to do it this way:

A B
1 Date
2 ="Jan,1" =dmax(H100:I465,2,A1:A2)
3 Date
4 ="Jan,2" =dmax(H100:I465,2,A3:A3)
5 Date
6 ="Jan,3" =dmax(H100:I465,2,A5:A6)

etc

Isn't there an easier way?

Thanks

Doug
 
D

Dave Peterson

Maybe you could use an array formula like:

=MAX(IF($H$100:$H$465=A1,$I$100:$I$465))

This assumes that the values (not just the formats!) are the same for H100:H465
and A1. This means that if you really wanted the max value for the Jan 1 (of
any year), you'd have to change the formula:

=MAX(IF(text($H$100:$H$465,"mmdd")=text(A1,"mmdd"),$I$100:$I$465))

(and make sure that both H100:H465 and A1 contain real dates.)

These are both 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.)

Adjust the range to match--but you can only use the whole column in xl2007.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top