Extract 7 and 30 day max

  • Thread starter Thread starter West22
  • Start date Start date
W

West22

Im stumped and I hope someone can help me.

I have 30 years of daily weather data and I am trying to write a formula to
extract 7 and 30 maximums. I need to do this for several workbooks.


Maybe I need to use SQL?

Can someone help me?

Thank you tons.
 
Sorry, I meant to say 7 and 30 day maximums.

My collum headings would be something like:

Date Temp Wind Speed Precip etc.

Im not sure if that is clear enough, I hope so.

Thanks again!
 
Hi,

Try this:

say your data starts in A2 and A8 represent the 7th day then in a blank
column type:
=SUM(B2:B8)
or
=AVERAGE(B2:B8)
depending on weather you want to sum or average the data, then drag down as
far as needed, that will give you the result for the preceding 7 days for
every day in your data, next use a combination of INDEX,MATCH and MAX to find
the corresponding 7 days max:

=TEXT(INDEX(A2:A20000,MATCH(MAX(F2:F20000),F2:F20000,0)-6),"mmm-dd-yyyy")&"
- "&TEXT(INDEX(A2:A20000,MATCH(MAX(F2:F20000),F2:F20000,0)),"mmm-dd-yyyy")

apply the same format for the rest of the fields and the 30 day max

Hope this helps!
Jean-Guy
 
Back
Top