Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Extract 7 and 30 day max
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Extract 7 and 30 day max
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! "West22" wrote: 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. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Extract 7 and 30 day max
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:F200 00,0)-6),"mmm-dd-yyyy")&" - "&TEXT(INDEX(A2:A20000,MATCH(MAX(F2:F20000),F2:F20 000,0)),"mmm-dd-yyyy") apply the same format for the rest of the fields and the 30 day max Hope this helps! Jean-Guy "West22" wrote: 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! "West22" wrote: 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
extract name | Excel Discussion (Misc queries) | |||
VBA - extract a value from many spreadsheets | Excel Discussion (Misc queries) | |||
How can I extract each Max key value ? | New Users to Excel | |||
Extract Unique Values, Then Extract Again to Remove Suffixes | Excel Discussion (Misc queries) | |||
Last Name, First Name extract | Excel Discussion (Misc queries) |