A Microsoft Office (Excel, Word) forum. OfficeFrustration

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.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » New Users
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

using dmax



 
 
Thread Tools Display Modes
  #1  
Old April 4th, 2010, 02:26 AM posted to microsoft.public.excel.newusers
dbasmb
external usenet poster
 
Posts: 6
Default 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  
Old April 4th, 2010, 04:05 AM posted to microsoft.public.excel.newusers
ozgrid.com
external usenet poster
 
Posts: 328
Default using dmax

Consider a PivotTable;
http://www.ozgrid.com/Excel/excel-pivot-tables.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  
Old April 4th, 2010, 01:10 PM posted to microsoft.public.excel.newusers
Dave Peterson
external usenet poster
 
Posts: 19,791
Default 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 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.

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

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 09:58 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.
Copyright 2004-2018 OfficeFrustration.
The comments are property of their posters.