Repost "On This Day"

[radio848]

Hi Fellahs:
Please excuse my double posting.
In XL 2000, I have a workbook called "Temperature". The current workbook
covers a record of temperatures since 1999. I've tried several ways to index
bt to no avail.
What I am trying to do is come up with a way that I can look at the minimum
and maximum temperature for this day (today), since 1999. Each day the min &
max temp would change to the date that corresponds to todays date.
Example: Today is, 8/22/03. The min temp for 8/22/1999 thru 2003 is 62
degrees. The same would apply to the maximum temp.
Thanks again!!

 

[Myrna Larson]

We really need to know EXACTLY how you have the data listed. Is it all in 3 columns (date, high,
low) on 1 worksheet? How have you entered the dates? Standard format and recognized by Excel as
a date? If so, maybe an array formula will work:


=MAX(IF((MONTH($A$2:$A$3660)=MONTH(TODAY()))*(DAY($A$2:$A$3660)=DAY(TODAY())),$B$2:$B$3660,-500))

entered with CTRL+SHIFT+ENTER.

The -500 is just an arbitrary, impossibly low, temperature.

To get the minimum, replace MAX with MIN and -500 with 500.

 

[Harlan Grove]

Please excuse my double posting.

Why couldn't you follow-up in the same thread? Unlike CDO users, it
shouldn't be hard for you to locate the existing thread.

In XL 2000, I have a workbook called "Temperature". The current workbook
covers a record of temperatures since 1999. I've tried several ways to index
bt to no avail.

Um, XL has more than 366 rows and 256 columns. With a different data
organization, this would be trivial. Starting in A3, enter the formulas

A3:
=TEXT(ROW()-2),"dd mmm")

and fill A3 down into A4:A368. Now you have each day of the year.

Enter some more formulas.

B1:
1999

B2:
=IF(MOD(COLUMN(),2),"High","Low")

D1:
=B1+1

Select B1:C1 and change the alignment to Center Across Selection. Do the
same for D1:E1. Copy D1 and paste into F1, H1, J1, etc. (every other
column). Fill B2 right as far as needed so that each year in row 1 has both
High and Low column labels in row 2.

The tricky part would be filling out the table with the data you already
have. Once done, the high and low temperatures on each row (I'll use row 3
for illustration) could be calculated using the following array formulas.

High:
=MAX(IF(MOD(COLUMN(B3:K3),2)=0,B3:K3))

Low:
=MIN(IF(MOD(COLUMN(B3:K3),2),B3:K3))

Since you only have 2 temperature readings per day, a row for each day of
the year, your data begins in 1999, and you're unlikely to still be doing
this (or anything else) when you run out of columns, this should help.

As for bashing your data into such a reorganized form, unless you provide
PRECISE *AND* COMPLETE DETAILS about how it's currently organized, there's
no way anyone else can help you no matter how many more times you repost
without details. It's up to you: provide details or figure this out without
help.

 

[Harlan Grove]

=MAX(IF((MONTH($A$2:$A$3660)=MONTH(TODAY()))
*(DAY($A$2:$A$3660)=DAY(TODAY())),$B$2:$B$3660,-500))

....

Simplify,

=MAX(IF(TEXT($A$2:$A$3660,"ddmmm")=TEXT(TODAY(),"ddmmm")),$B$2:$B$3660,-500)

 

[Harlan Grove]

It is a workbook. sheet1=1999,sheet2=2000, etc., thru 2005. 1st column is
date, 2nd column is low temp, 3rd column is day of week, 4th colum is high
temp.
I also keep track of rain and snowfall. But that is unimportant right now.
What I am looking for is a way to reference history: So that on today's date
two years ago I can see the high and low temps for that particular date
without sifting through worksheets.

You can't index worksheets and rows or columns in a single INDEX function
call. However, you could do this.

=INDEX(INDIRECT(LOOKUP(Y,{1999;2000;2001;2002;2003;...},
{"Sheet1";"Sheet2";"Sheet3";"Sheet4";"Sheet5";...})&"B1:B366"),
ROW())

where Y represents one of the years. This formula gives the corresponding
column B entry from the worksheet matching the year Y for the same row in
which you enter this formula.

Myrna already gave you a formula to pull the max historical temperature.