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.

Here's a sample of the data I have:

A B C
Date Max Min
1 01Jan11 0 -18
2 02Jan11 3 -15
3 03Jan11 4 -16
...
365 01Jan12 5 -13
366 02Jan12 -5 -23
367 03Jan12 -10 -30

On other sheet I have:

A B C D
MaxMax MinMax MinMin MaxMIn
01Jan 5 0 -18 -13
02Jan 3 -5 -23 -15
03Jan 4 -10 -30 -16

I achieve the above by a formula such as =Max(a1,a365) or Min(a1,a365) and so on for all of the past January 1st's so that I have the data that I'm looking for.

What I'd like to see is if I could do something like the following:

A B C D E F G
MaxMax Date MinMax Date MinMin Date MaxMIn
01Jan 5 01Jan12 0 01Jan11 -18 01Jan11 -13
02Jan 3 02Jan11 -5 02Jan12 -23 02Jan12 -15
03Jan 4 03Jan12 -10 03Jan12 -30 03Jan12 -16

Since I have several January 1st's I'll know on which one it occured.

Since I know the value, I tried doing a look up on that value in that dates cells, but I can't seem to get that to work. Here's what I thought would work, but it obviously does not:

=VLOOKUP(B10932,'WX Record'!{D575,D1279,D1644,D2009,D2374,D3075,D3440, D3805,D4171,D4536,D4901,D5266,D5632,,D213,D913,D27 22,D5992,D6357,D6722,D7088,D7453,D7818,D8183,D8549 ,D8914,D9279,D9644},1,FALSE)

Is there a different way to look up a value in different cells that are not contiguous? Is there a different approach I can take to get the data that I want?

Thank you for your time and assistance!

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,D5 267,D5633,D914,D2010,D5993,D6358,D6723,D7089,D7454 ,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!

Hi,

Am Wed, 12 Dec 2012 13:41:38 +0000 schrieb StaffHerb:


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

Max first of January:
=MAX((DAY(A1:A10000)=1)*(MONTH(A1:A10000)=1)*D1:D1 0000)
and enter the array formula with CTRL+Shift+Enter


Regards
Claus Busch
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

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.

OK. I've created a sample that contains just the relevant columns as they are in my main spreadsheet:

http://stormmonitoring.com/sample/We...taExample.xlsx

The first 10000 columns contain the data and columns 10000-10381 contain the daily numbers (averages, max's & min's).

I'm looking to create a table below that that will have the records and the date which they occurred as described above.

Thanks again for your help!

Dave

On Thu, 13 Dec 2012 13:39:18 +0000, StaffHerb wrote:


'Ron Rosenfeld[_2_ Wrote:
;1608022']
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.

OK. I've created a sample that contains just the relevant columns as
they are in my main spreadsheet:

http://stormmonitoring.com/sample/We...taExample.xlsx

The first 10000 columns contain the data and columns 10000-10381 contain
the daily numbers (averages, max's & min's).

I'm looking to create a table below that that will have the records and
the date which they occurred as described above.

Thanks again for your help!

Dave

I think the following formulas will work for determining the values. I used NAME'd ranges

DateColumn =Sheet1!$A$4:$A$10010
MaxTemp =OFFSET(DateColumn,,3)
MinTemp =OFFSET(DateColumn,,4)

But, of course, the formulas need to take the blanks into account in the temperature columns. As near as I can tell, the differences between these results and what you have written down on your sheet are due to you not including all of the dates with temperatures in your "range".

These formulas must be **array-entered**:

The all refer to your Jan 1 entry in the results part of your sheet.

AverageMax:
=SUM((MONTH(DateColumn)=MONTH($A10016))*(DAY(DateC olumn)=DAY($A10016))*MaxTemp)/
SUM((MONTH(DateColumn)=MONTH($A10016))*(DAY(DateCo lumn)=DAY($A10016))*ISNUMBER(MaxTemp))

AverageMin:
=SUM((MONTH(DateColumn)=MONTH($A10016))*(DAY(DateC olumn)=DAY($A10016))*MinTemp)/
SUM((MONTH(DateColumn)=MONTH($A10016))*(DAY(DateCo lumn)=DAY($A10016))*ISNUMBER(MinTemp))

HighMax:
=MAX((MONTH(DateColumn)=MONTH($A10016))*(DAY(DateC olumn)=DAY($A10016))*ISNUMBER(MaxTemp)*MaxTemp)

LowMax:
=MIN((MONTH(DateColumn)=MONTH(A10016))*(DAY(DateCo lumn)=DAY(A10016))*ISNUMBER(MaxTemp)*MaxTemp)

LowMin:
=MIN((MONTH(DateColumn)=MONTH(A10016))*(DAY(DateCo lumn)=DAY(A10016))*ISNUMBER(MinTemp)*MinTemp)

HighMin:
=MAX((MONTH(DateColumn)=MONTH(A10016))*(DAY(DateCo lumn)=DAY(A10016))*ISNUMBER(MinTemp)*MinTemp)

----------------------------------------

To **array-enter** a formula, after entering
the formula into the cell or formula bar, hold down
<ctrl<shift while hitting <enter. If you did this
correctly, Excel will place braces {...} around the formula.

To get the First MaxTemp Date: (also array entered)
=INDEX(DateColumn,MATCH(1,(MONTH(DateColumn)=MONTH ($A10016))*(DAY(DateColumn)=DAY($A10016))*(MaxTemp = cell_ref for MaxTemp (e.g. D10016)),0))

To get Last MaxTemp Date: (entered normally)
=LOOKUP(2,1/((MONTH(DateColumn)=MONTH($A10016))*(DAY(DateColum n)=DAY($A10016))*(MaxTemp=cell ref for MaxTemp(e.g. D10016))),DateColumn)

To get the first and last "Jan 1" of the other parameters, merely substitute the appropriate temperature computations, being sure to change references from MaxTemp to MinTemp if you are looking, for example, for Average Jan 1 MinTemp dates, etc.