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
0
Utf
4/4/2010 1:26:01 AM
excel.newusers 15348 articles. 2 followers. Follow

2 Replies
765 Views

Similar Articles

[PageSpeed] 32

Consider a PivotTable;
http://www.ozgrid.com/Excel/excel-pivot-tables.htm



-- 
Regards
Dave Hawley
www.ozgrid.com
"dbasmb" <dbasmb@discussions.microsoft.com> wrote in message 
news:CE043D9A-7EE4-46F4-B6A6-CEE171F82D7B@microsoft.com...
>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 

0
ozgrid
4/4/2010 3:05:27 AM
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
0
Dave
4/4/2010 12:10:55 PM
Reply:

Similar Artilces:

dependant lists using dynamic data
Hello All, I have seen this question asked a number of times, but none of the suggested solutions fit my needs. Any other suggestions are greatly appreciated. I have a Worksheet that contains data that is exported from another application. The data has the following format: COL 1 COL 2 ---------- ---------- A 1 A 2 A 3 A 4 B 5 B 6 B 7 C 8 C 9 C 10 C 11 On another worksheet, I need to use data validation to force users to select t...

Outlook XP/ No profiles have been created. To create a new profile, use the Mail
I'm using a program called mapimail to create a new email and attach a file automatically. It works on every computer buy mine. I'm running Windows XP and Outlook XP. The error I get is : No profiles have been created. To create a new profile, use the Mail icon in the Control Panel. But I have a profile. What could be the problem? Thanks ...

Using "Like" Operator in "Select Case" Statement
I want to use a "like" operator in a Select Case statement; I am sure that there is some way to do something like this. Here is what I have to do: Select Case ClassOfContact Case "Bob's client", "Mary's client", "Our clients". . . . . ...... Case "Vendors" ...... End Select Obviously, it would be safer and faster to use something like a Like operator, such as "Like "*client*", but Access 2007 rejects the Like operator. Is there a way to do that? Is this a limitation of Select Case? ...

formula help
I am working on a spreadsheet and need help developiung a formula (my excel knolwedge is very basic).... In y2, I want a formula that subtracts w2 from x2 but if x2 is > w2 I want y2 to reflect the number in w2. Can anyone help me with that? I will be eternally grateful! I THINK I JUST FIGURED IT OUT!! THE FORMULA I ARRIVED AT IS =IF(X2>W2, W2,SUM(W2,-X2)) DOES THIS SEEM CORRECT? I USED CSE WHEN I ENTERED THE FORMULA You sure that's what you want? You will always get a zero or negative number in Y2 if X2<=W2 =IF(X2>W2,W2,X2-W2) Gord Dibben MS Exc...

Importing from Outlook to CRM using a custom Contact form in Outlook
The subject says it all. I'm trying to import contacts from Outlook into CRM using Scribe. We have a custom contact form for Outlook contacts, and Scribe isn't picking up the custom fields. Any ideas? I would post a question on the Scribe support forum <joshm@susqtech.com> wrote in message news:1120136692.551538.88360@g43g2000cwa.googlegroups.com... > The subject says it all. I'm trying to import contacts from Outlook > into CRM using Scribe. We have a custom contact form for Outlook > contacts, and Scribe isn't picking up the custom fields. Any ideas? > ...

Exchange 2003 OWA cannot locate distribution lists using 'find option'
We used Outlook Web Access 2003 on Exchange 2003. Some of our users access a shared mailbox using OWA. There is an absolutely vast contacts folder associated with it, and they need to be able to search through it for addresses. Unfortunately, we have found that if we put in part of the name of the contact in the 'to' field of the email, then it can be resolved. However, if they click on the 'find' button and put part of the name of the DL, OWA cannot find it. They cannot always just type the name in the 'to' field and then click the resolve button as sometimes they do ...

taking email from body, using as reply-to automatically?
I get emails from a form on a website, this is how they appear in outlook 2003: http://img118.imageshack.us/img118/1194/untitledwe0.jpg How do I use the included email in the message body as the reply-to address automatically? Thanks. See my response to your post in another group. --=20 Sue Mosher, Outlook MVP Author of Microsoft Outlook 2007 Programming: Jumpstart for Power Users and Administrators http://www.outlookcode.com/article.aspx?id=3D54=20 "lrbtav" <lrbtav@gmail.com> wrote in message = news:1181853613.397275.151420@q19g2000prn.googlegroups.com... >...

Using Xsl to group by unique values
I'm not sure if this is the right forum to ask this, seeing this problem is pretty much straight XSL, but here goes. I'm writting a simple employee database, and I want to be able to: A) get a node-set returning only unique values based off of a query. (the names of all the managers) b) loop through each of the nodes, sorted by the list of the managers. My Xml looks something like this: <root> <employee id="001"> <manager id="01"/> </employee> <employee id="002"> <manager id="01"/> &l...

Problem with query using linked tables
I have the following problem: I have a select query which uses two tables linked to SQL Server via ODBC. When I run it for the first time, I get the SQL Server login prompt (which is normal, since I didn't persist the login information when I linked the tables). The problem comes when I browse the results. Access randomly shows the login prompt again. One of the fields within the query is calculated via a vba function, and if I cancel the login, I get a vba error. If I debug the error, the code window shows the vba function I was talking about, and the execution is stopped in a lin...

"Refresh" problems with using Outlook COM interface
We've been running into the following bug. We have a program that uses the Outlook and the CDO COM interfaces. It does most things through the Outlook interface, but it does a few things through the CDO interface, and it maintains both the Outlook NameSpace and the CDO Session connections open continuously. But, I believe most of the operations in the scenario below are done through the Outlook interface. Scenario: 1. Program logs in to Outlook using Profile A. 2. Program creates a TaskItem. User B (a person, not a program) has access to this TaskItem through his own profile, Profile ...

How to use Terry Kreft's API functions
http://www.mvps.org/access/api/api0049.htm API: Copy variables/control contents to memory Hi, I've copied the code to standard module. Unsure of what functions are available. Also, do I always have to open the clipboard then close it? Please give examples of how to use or where I can find a list of all functions. Thanks. > Please give examples of how to use or where I can find a list of all > functions. Thanks. The lsit of fuctiosn is in the text you linked to. just take a quick skim of the function ames. The example code is for grabbing, or place a value into the clip...

I want use accounting software in INDIA so pls help me for use
Hello Sir, I want use microsoft accounting software in india so pls help me for use in india. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree" in the message pane. http://www.microsoft.com/office/community/en-us/default.mspx?mid=aacd64ad-85b7-4906-8088-149b0963567b&dg=microso...

Using the same address & Contacts folders for Outlook and Outlook Express
I am having problems getting outlook express to recognize all of my contacts and address book in Microsoft Outlook. Do you have any insite on how I can fix it. I'm running windows XP, Outlook Express 6 and MS Outlook 2002 (Outlook10) Regards, Murray There is no such feature natively with Outlook 2002 and above. You can force the WAB to use your Contacts Folder with a reg hack: http://www.slipstick.com/contacts/oeshare.htm -- Russ Valentine [MVP-Outlook] "Murray" <anonymous@discussions.microsoft.com> wrote in message news:1bc801c3e0c0$f5c0c5e0$a301280a@phx.gbl... ...

PC won't hibernate when Outlook open says Word in use
Hi, Recently Outlook 2002 has stopped Laptop from hibernating. Dialog box says network resources active, specifically Word, these have to be stopped , and then hibernation can continue. 1. Laptop is standalone. 2. Word is not running, but is present on the Laptop. 3. Closing Outlook solves the problem. Any ideas why this is happening? Cheers Rob PS. Word is NOT the e-mail editor anymore, use the HTML option. Have you verified that indeed the winword.exe process is not running on the Processes tab in Task Manager? (CTRL+SHIFT+ESC) Which version of Windows are yo...

Trying to use the INDIRECT funtion with a relative Row reference
I have a worksheet where I have rows of information. Each row is made up of several columns of information. I am constantly updating the information by inserting cells and moving the old cell to the right. I am trying to use the INDIRECT function to reference the "G" column but I need the row number to be relative because I sort the worksheet based on the information I get back. INDIRECT works great until I sort then it is still refering to the row where the information WAS at. . .not where it is NOW. I hope this makes sense and any help would be greatly appreciated. Tha...

Using CountIf to get values between two values
Can CountIf be used to get values between two values? All the examples use criteria like ">30","<20", etc. How would you count values between 10 and 100, say? I tried a few guesses at syntax but none of them worked. Thanks tom Use SUMPRODUCT: =SUMPRODUCT((A1:A999>=10)*(A1:A999<100),B1:B999) HTH -- AP "THOMAS CONLON" <someone@verizon.net> a �crit dans le message de news: f1Icg.7932$Ar6.6993@trnddc02... > Can CountIf be used to get values between two values? All the examples > use criteria like ">30","<20&qu...

Cannot replace imageres.dll (win\system32), File in Use?
Trying to change the "imageres.dll" file to remove a stupid theme this computer came with. I've modified the file using ResHacker using all the proper image resolutions (and the images I want, the regular vista ones), but it will not let me overwrite the file. It seems to have nothing to do with permissions (they are all enabled properly), but I get the error as "Can not overwrite this file because it is in use by another program". What else can I do to try and replace it? -- KBeck Posted via http://www.vistaheads.com "KBeck" <KBe...

Calculate an age from a birthdate using a specific date in time
I have a table containing student birthdates (yyyymmdd). This information was collected Spring 2007. I need to calculate the age of the student at that time. Could you please help me create a calculated field for this table that displays the age. Thanks. Karen, Take a look at http://www.cardaconsultants.com/en/msaccess.php?lang=en&id=0000000011#age it should be what you are looking for. -- Hope that helps, Daniel Pineault "karen k-12" <karen k-12@discussions.microsoft.com> wrote in message news:FD6B2D8D-D521-4D2A-846F-D17F31DA2B20@microsoft.com... >I h...

Inserting Signature When Using Outlook Templates
Outlook 2002 - I have created a number of email templates (*.oft) and placed them on a network drive for general office use. End users have created a menu bar and added new commands (Category File/Command Mail Message) and used the hyperlink feature to use their choice of templates. The problem is that their signature does not appear at the bottom even though it is a new email (note if they turn off the MS Word to edit feature their signature does appear but this is not a favorable option for them). > Any suggestions as to how they can use the template, keep MS Word to edit and ...

When to use ThreadPool
Hi! Here is some text from e-learning. What does it mean when the text says "don't require scheduling of your thread's execution" ? *ThreadPool class is best suited for use when your threads are performing short tasks or when you don't require scheduling of your thread's execution.* //Tony Forget this question //Tony "Tony Johansson" <johansson.andersson@telia.com> skrev i meddelandet news:%23z5nXh58KHA.3276@TK2MSFTNGP02.phx.gbl... > Hi! > > Here is some text from e-learning. What does it mean when the text says...

Sharepoint 2007
Hello, Been searching for 3 hours and getting no where - some what of a newbe.. What am I trying to do ? On Sharepoint 2007 Wish to access Excel files stored on a file server using UNC paths - simple or so I thought... have set up trusted file location eg file:\\fileserver1\files type UNC trust children =Yes Then I go to a PC - access the web site - edit Excel web component go to browse a workbook location type file:\\fileserver1\files in the URL press the arrow icon "Cannot find this location in the current site collection" When I try the "...

Designing a yearplanner using publisher
Please help me design a yearplanner that could look like this: A table that would list the month, days (monday - Friday) and blocks for me to use for information. thanx brenda Go here: http://office.microsoft.com/templates/ Most of these are in Word, but you could adapt them to suit your needs. -- Mary Sauer MS MVP http://office.microsoft.com/clipart/ http://mvps.org/msauer/ "brenda" <bgamba@uwc.ac.za> wrote in message news:058601c38101$89e0fe40$a001280a@phx.gbl... > Please help me design a yearplanner that could look like > this: > > A table that would list...

Opening an edit form using linkcriteria
Hi I have a form which displays data as follows: Country State City Location Population CrimeRate US IL Chicago LocA 20000 10 US IL Chicago LocB 10000 4 US NY NY City 40000 2.5 The above form is attached to a table with composite primary key as Country, State, City and Location. These four keys together make a unique row. I need to come up with an edit form on the onclick event of the country column so that I can display a row of data in a form and be able to edit population and crimerate data. Previously I have use...

Creating Formula using check boxes
Is it possible to create a formula using various check boxes? For example, if a check box relating to a particular month is checked, then that is added to the formula. The formula will then be slowly built according to what has been checked. This will be using the SUMPRODUCT formula.. Any ideas/advice appreciated.. Hi if your checkboxes are for example linked to row 2 you could use something like =SUMPRODUCT(--(A2:X2),A3:X3) to sum row 3 depending on the checked columns -- Regards Frank Kabel Frankfurt, Germany "Anthony Slater" <AnthonySlater@discussions.microsoft.com>...

Using the SUM Formula
How do I create a formula that adds from the top of a column? Here's the situation. I have a column of numbers (with a header of "Pay"). To the right of that colum I want to keep a running total ("Yearly Total"). Every time I get paid, I'm going to enter the amount in the pay column and then I want the Yearly Total column to automatically add it. When I create a fomula (say =SUM(B2:B21)), and propigate it down, it doesn't start at the top. In others words the next formula is =SUM(B3:B22). I want it to be =SUM(B2:B22). And, if a value is not filled in on the ...