Lookup in multiple & separate cells

[StaffHerb]

I have a spreadsheet of weather data that I've collected over the pas
30 years. Among other data, the columns on one sheet have a date, Hig
Temperature & Low Temperature. On another sheet I have the days of th
year listed along with the highest High Temperature, lowest Hig
Temperature, etc. This gives me the value for each date, but I'
wondering if there is a way that I can also get the last date on whic
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 tha
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 tha
dates cells, but I can't seem to get that to work. Here's what
thought would work, but it obviously does not:

=VLOOKUP(B10932,'W
Record'!{D575,D1279,D1644,D2009,D2374,D3075,D3440,D3805,D4171,D4536,D4901,D5266,D5632,,D213,D913,D2722,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 ar
not contiguous? Is there a different approach I can take to get the dat
that I want?

Thank you for your time and assistance

 

[Ron Rosenfeld]

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.

 

[StaffHerb]

;1607949']On Mon, 10 Dec 2012 16:20:27 +0000, StaffHer

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 o 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 reformatte
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 colum
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 fa
is:

D1280,D1645,D2723,D3441,D3806,D4172,D4537,D4902,D5267,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 you
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 an
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,D7454,D7819,D8184,D8550,D8915,D9280,D9645)=MAX(D1280,D1645,D2723,D3441,D3806,D4172,D4537,D4902,D5267,D5633,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 indee
gives me the date that has the maximum value in column B so I know i
works. I guess I need to figure out how to get the maximum value in th
selected cells instead of the entire column. Any direction if muc
appreciated!

Thanks again

 

[Claus Busch]

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)*D110000)
and enter the array formula with CTRL+Shift+Enter


Regards
Claus Busch

 

[Ron Rosenfeld]

;1607949']On Mon, 10 Dec 2012 16:20:27 +0000, StaffHerb

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,D5267,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,D7454,D7819,D8184,D8550,D8915,D9280,D9645)=MAX(D1280,D1645,D2723,D3441,D3806,D4172,D4537,D4902,D5267,D5633,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.

 

[StaffHerb]

;1608022']
You won't be able to construct a discontinuous array to use in th
LOOKUP function the way you are trying to do. If you could upload
sample file to a public site (e.g. Skydrive) and post a link here,
would be happy to look at it further. What you want can be done; it'
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 a
they are in my main spreadsheet:

http://stormmonitoring.com/sample/WeatherDataExample.xlsx

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

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

Thanks again for your help!

Dav

 

[Ron Rosenfeld]

;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/WeatherDataExample.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(DateColumn)=DAY($A10016))*MaxTemp)/
SUM((MONTH(DateColumn)=MONTH($A10016))*(DAY(DateColumn)=DAY($A10016))*ISNUMBER(MaxTemp))

AverageMin:
=SUM((MONTH(DateColumn)=MONTH($A10016))*(DAY(DateColumn)=DAY($A10016))*MinTemp)/
SUM((MONTH(DateColumn)=MONTH($A10016))*(DAY(DateColumn)=DAY($A10016))*ISNUMBER(MinTemp))

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

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

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

HighMin:
=MAX((MONTH(DateColumn)=MONTH(A10016))*(DAY(DateColumn)=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(DateColumn)=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.