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.