On This Day!

  • Thread starter Thread starter radio848
  • Start date Start date
R

radio848

Hello Fellahs:
Nothing professional about what I am doing. But I have a spreadsheet in xl
2000, that i keep a record of the temperature. (Min & Max). Been doing it
for years.
What I am after is; to lookup this date for a number of past years that will
tell me what the high and low temps were for this day. (Min & Max).
I understand that it is an index type formula, but all attempts have failed.
Thanks!
 
Hi Wayne,

=MAX(IF((MONTH($A$1:$A$10)=MONTH(TODAY()))*(DAY($A$1:$A$10)=DAY(TODAY())),$B
$1:$B$10))

=MIN(IF((MONTH($A$1:$A$10)=MONTH(TODAY()))*(DAY($A$1:$A$10)=DAY(TODAY())),$B
$1:$B$10))

where A1:A10 contain the dates and B1:B10 contain the temperatures.

These are array formulas, so you need to hit CONTROL-SHIFT-ENTER
simultaneously, instead of just ENTER.

Rick
 
You are correct. In the first message he said "I have a spreadsheet". When I
replied to his 2nd message where he talked about "multiple worksheets", I,
too, used the term worksheet. "Taking liberties", I guess <g>.

Anyway, I guess what he has is a workbook with a sheet for each year. Not an
easy layout for this problem due to the Feb 29 issue. Do you have any
suggestions other than skipping that date or fudging it in non-leap years? I
think I would omit it, and when the current date is Feb 29, promote it to
Mar 1 in the formula.

Since the year is determined from the worksheet name, I envisioned putting
the date in as text, e.g. 'Jan 01 or '01/01 instead of a "real" date.

Then he could do a vlookup on TEXT(TODAY(),"mmm dd") or
TEXT(TODAY(),"mm/dd") to find the correct row. As long as there are always
365 or 366 "dates" on each sheet, a 3-D formula would be easy.
 
Hi Gals & Guys:
Let's try again, if you are willing.
I have changed the workbook. All of the temp references are now on one
worksheet. Total number of lines are 3020. Col a =date dd/mm/yy; col b =low
temp;col c= day name (monday, tuesday, etc); col d= high temp. There are
total and averages at each break between months. The goal is; on today's
date a year ago, or two years ago, going back to 1999; i would like the low
& high temp to be displayed automatically.
Placement of the displayed info is arbitrary:
What do you think.
Thanks again!!
 
Which brings us back to the first answer, with a slight variation in the
references:

=MAX(IF((MONTH($A$2:$A$9999)=MONTH(TODAY()))*(DAY($A$2:$A$9999)=DAY(TODAY())
),$D
$2:$D$9999))

=MIN(IF((MONTH($A$2:$A$9999)=MONTH(TODAY()))*(DAY($A$2:$A$9999)=DAY(TODAY())
),$B$2:$B$9999))

These are array formulas, so you need to hit CONTROL-SHIFT-ENTER
simultaneously, instead of just ENTER.

Also note: column A cannot contain text, only dates (which is why I excluded
row 1, assuming you have a heading on the columns. I also used 9999 as the
end row, assuming that the table will continue to grow past row 3020.
 
Hello Rick:
Managed to solve the task.
This is what was accomplished;
65.7 8/24/2002 106.3 8/24/2000

Thank you for your trouble and Very Good Suggestions.!! Had to alter things
a bit to suit my wants, but all is well now AND (AUTOMATED)..
Thanks again, and good luck to you and your endeavors..
 
It's not very elegant, but here's my first pass at it:

=MIN(VLOOKUP(EDATE(TODAY(),-12),$A$8:$D$30,2,FALSE),VLOOKUP(EDATE(TODAY(),-2
4),$A$8:$D$30,2,FALSE),VLOOKUP(EDATE(TODAY(),-36),$A$8:$D$30,2,FALSE),VLOOKU
P(EDATE(TODAY(),-48),$A$8:$D$30,2,FALSE))

=MAX(VLOOKUP(EDATE(TODAY(),-12),$A$8:$D$30,4,FALSE),VLOOKUP(EDATE(TODAY(),-2
4),$A$8:$D$30,4,FALSE),VLOOKUP(EDATE(TODAY(),-36),$A$8:$D$30,4,FALSE),VLOOKU
P(EDATE(TODAY(),-48),$A$8:$D$30,4,FALSE))

Problem: If today's date does not appear for *any* of the past four years,
you will receive a #N/A error.

You can adapt it for multiple worksheets by simply changing the referenced
cells. You will have the same #N/A error either way.

Let me know if this is acceptable or if I should really put my thinking cap
on.
 
Mr. Rick:
You are a genius sir.
Of course I needed to adjust the formulae to suits my needs, but it works
just fine.
I really appreciate people who like a challenge and are cardial at the same
time.
Many thanks to you and take care.
 
Back
Top