MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Lookup Date of Max Temp


Posted by Riki Phelan on August 24, 2001 8:10 AM

Hi all - This formula suggested by Aladin, with cell references changed by me, is soooo frustratingly close to solving all my problems!

This formula selects and displays the range address of months from a list of 365 days, labels are B1:Date and C1:Temps

=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))

e.g. if I enter "3" for March in cell F2, it returns C62:C92 - the correct range of March days.
BUT - when I enter "1" for January, it returns C2:C62 which is the range for both Jan and Feb. You'd expect it to return C2:C32 for Jan. All other months work fine, just January seems to return 60 days.

Aladin, or anyone else - can you offer a solution? As I said, this is so very close to a final solution to my problem.

"Desperate and dateless" - until I sort this out at least!

Cheers, Riki

======================
Posted by Aladin Akyurek on August 22, 2001 at 10:26:05:

In Reply to: Lookup Max Date posted by Riki Phelan on August
21, 2001 at 22:55:52:

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,3568
6,12;9,35693,12;9,35700,3;10,35707,4;10,35714,5;10,35721,4;1
0,35728,5;11,35735,6;11,35742,3;11,35749,7;11,35756,4;11,357
63,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))&":"&ADDRE
SS(MAX((MONTH($B$2:$B$20)=E2)*ROW($C$2:$C$20)),COLUMN(C1))
In G2 array-enter:
=INDEX($B$1:$B$20,MAX((INDIRECT(F2)=MAX(INDIRECT(F2)))*ROW(I
NDIRECT(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: You probably noticed that I didn't use column A that
houses the month numbers. For the above computations that
column is superfluous.

Aladin

======================
as:
from my filtered list?
=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.
returned from a filtered list? I'm at a loss...


Posted by Aladin Akyurek on August 24, 2001 8:48 AM

Riki,

I extended my example sample so that it starts at 1-Jan-01 and ends at 31-Dec-01.

Adjusted just the data ranges within the array formula that computes ranges:

=ADDRESS(MATCH(E2,MONTH($B$1:$B$366),0),COLUMN(C1))&":"&ADDRESS(MAX((MONTH($B$2:$B$366)=E2)*ROW($C$2:$C$366)),COLUMN(C1))

Entered 1 in E2 as criterion month.

The result that appears in F2 is:

$C$2:$C$32

as you expected!

Aladin

=====================

Posted by Riki on August 24, 2001 10:06 AM

Hi Aladin,

Thanks so much for your help with this one, and the speedy response. The problem was my doing - I left a blank line for Feb 29 to cater for future leap years, which I now deleted.

The formulas didn't like that, but all sorted now.

We are finally done, thanks again Aladin!

Cheers, Riki
"Just dateless now!"