If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. 


Thread Tools  Display Modes 
#1




using dmax
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 
Ads 
#2




using dmax
Consider a PivotTable;
http://www.ozgrid.com/Excel/excelpivottables.htm  Regards Dave Hawley www.ozgrid.com "dbasmb" wrote in message ... 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 
#3




using dmax
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 ctrlshiftenter 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 matchbut you can only use the whole column in xl2007. dbasmb wrote: 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  Dave Peterson 
Thread Tools  
Display Modes  

