On Wed, 12 Dec 2012 13:41:38 +0000, StaffHerb wrote:


'Ron Rosenfeld[_2_ Wrote:
;1607949']On Mon, 10 Dec 2012 16:20:27 +0000, StaffHerb
wrote:
-
I have a spreadsheet of weather data that I've collected over the past
30 years. Among other data, the columns on one sheet have a date,
High
Temperature & Low Temperature. On another sheet I have the days of
the
year listed along with the highest High Temperature, lowest High
Temperature, etc. This gives me the value for each date, but I'm
wondering if there is a way that I can also get the last date on which
that value occurred.-

It's hard to tell your layout due to how the newsgroup has reformatted
things. So let's assume that

Dates are in column A
Max temps are in column B
Min temps are in column C

The MaxMax Temp would be =max(b:b)
The MinMax Temp would be =min(b:b)

The date for the first MaxMax Temp would be
=INDEX(A:A,MATCH(MAX(B:B),B:B,0))

The date for the last MaxMax Temp would be
=LOOKUP(2,1/(B:B=MAX(B:B)),A:A)

For MinMax replace max(b:b) with min(b:b)

And for the MaxMin and MinMin parameters, refer to C:C instead of B:B

And you can replace the whole column references with partial column
references, if you like.

Thank you so much for this! My actual columns are

Column A = Dates
Column D = Max Temp
Column E = Min Temp

One of my arrays that equates to all January 1sts that I have so far
is:

D1280,D1645,D2723,D3441,D3806,D4172,D4537,D4902,D 5267,D5633,D914,D2010,D5993,D6358,D6723,D7089,D745 4,D7819,D8184,D8550,D8915,D9280,D9645

I'd prefer having the last time record value occurred so I used your
example of =LOOKUP(2,1/(B:B=MAX(B:B)),A:A)

I'm not too familiar with the LOOKUP function but I tried using and
playing around with the example and I can't quite get it to work.
Here's what I tried:

=LOOKUP(2,1/((D1280,D1645,D2723,D3441,D3806,D4172,D4537,D4902, D5267,D5633,D914,D2010,D5993,D6358,D6723,D7089,D74 54,D7819,D8184,D8550,D8915,D9280,D9645)=MAX(D1280, D1645,D2723,D3441,D3806,D4172,D4537,D4902,D5267,D5 633,D914,D2010,D5993,D6358,D6723,D7089,D7454,D7819 ,D8184,D8550,D8915,D9280,D9645)),A:A)

This gives me a #VALUE! error.

I will say that if I use the example without modification, it indeed
gives me the date that has the maximum value in column B so I know it
works. I guess I need to figure out how to get the maximum value in the
selected cells instead of the entire column. Any direction if much
appreciated!

Thanks again!

You won't be able to construct a discontinuous array to use in the LOOKUP function the way you are trying to do. If you could upload a sample file to a public site (e.g. Skydrive) and post a link here, I would be happy to look at it further. What you want can be done; it's just a matter of being able to see the data in the proper format.