MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Lookup Max Date


Posted by Riki Phelan on August 21, 2001 10:55 PM

I have a list of daily data for a year in cols A, B, and C as:

-Month- -Date- -Temp-

Using AutoFilter, I display a particular month.
I extract the max temp with =SubTotal(4,C4:C369)

How am I able to extract the date that max temp occured from my filtered list?

This works: =SUBTOTAL(1,INDEX(B4:B369,MATCH(C375,C4:C369,0),1)) but only if the temp is unique in the year. It returns an error if I display all of July. This is becuase I have 30.0 on Jan 10, and 30.0 on Jul 10.

Anyone know how I can get the date of the max temp returned from a filtered list? I'm at a loss...

Tks, Riki


Posted by Mark W. on August 22, 2001 8:04 AM

You've already used an Autofilter to isolate the
month of interest. Is the some reason you don't
want to apply a Top 10 filter on Temp to return the
Top 1 Items?

Posted by Aladin Akyurek on August 22, 2001 10:26 AM

Riki,

Although using filtering is a good idea, one drawback is that it's not fully automatic. The system of formulas that follow, while a bit complicated, does not have this drawback. Moreover, as I understood from your initial post (you were talking about prices on Fridays of every month, not about temperatures), you wanted to produce min's and max's with respect to each month. The formulas below would allow you to do that too.

Lets start with some data. The following sample

{"month","date","price";8,35665,1;8,35672,2;9,35679,4;9,35686,12;9,35693,12;9,35700,3;10,35707,4;10,35714,5;10,35721,4;10,35728,5;11,35735,6;11,35742,3;11,35749,7;11,35756,4;11,35763,2;12,35770,4;12,35777,1;12,35784,5;12,35791,2}

occupies the range A1:C20. The numbers like 35672 are just dates and show up here as they are internally stored in Excel, so don't worry about them.

In E2 enter: 9 [ month (number) of interest

In F2 array-enter: =ADDRESS(MATCH(E2,MONTH($B$1:$B$20),0),COLUMN(C1))&":"&ADDRESS(MAX((MONTH($B$2:$B$20)=E2)*ROW($C$2:$C$20)),COLUMN(C1))

Note 1. In order to array-enter a formula, you need to hit CONTROL+SHIFT+ENTER at the same time (not just ENTER).

In G2 array-enter: =INDEX($B$1:$B$20,MAX((INDIRECT(F2)=MAX(INDIRECT(F2)))*ROW(INDIRECT(F2))))

This gives you the date on which the price/temp is the highest in month 9.

In H2 enter: =MAX(INDIRECT(F2))

This gives you the highest price/temp in month 9.

Note 2. It should be clear by now that you can list all month numbers in E from E2 on and apply/copy the above formulas for all of them.

Note 3. The above system of formulas can also be adapted for use in a different worksheet than the sheet in which you have your data.

Note 4. These formulas, suitably adapted, can also give you min values for any month.

Note 5. You probably noticed that I didn't use column A that houses the month numbers. For the above computations that column is superfluous.

Aladin

===========

Posted by Riki Phelan on August 22, 2001 7:12 PM

Hi Mark and Aladin,

Thanks Aladin for your response, I'll try that later on. Sorry for the confusion between Temps and Prices - I've got 2 projects on the go, one a database of weather stats, the other a "weekly pricewatch" and they are ending up very similar.

Mark - yep I realise I can get the High and Low values thru filtering, but I'm after the Highest and Lowest values, together with corresponding dates, to be return in cells at the bottom of the list. These values and dates are then refered to by other sheets.

I've been trying to nut this one out for ages now.

Thks Riki

Posted by Aladin Akyurek on August 23, 2001 1:33 AM

Yep. The system of the formulas I described applies to both projects.

Posted by Riki Phelan on August 23, 2001 6:40 PM

Hi Aladin!

You're a gem! Your solution does extactly what I want. Thanks very much!

Cheers, Riki