Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Making 5 Minute Wind Averages
Right now, I have data that is in 1 minute wind speed and direction (in
degrees) averages. I need to convert it to 5 minute averages (that is the average from time 0-5min, 6-10min, 11-15 min and so on). I've attempted to use the text and indirect functions to do this, but it ends up over lapping (i get averages from 0-5, then 5-9, then 9-13). What kind of code or functions can I write to make this work? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Making 5 Minute Wind Averages
On Sat, 10 Nov 2007 11:22:01 -0800, Larissa
wrote: Right now, I have data that is in 1 minute wind speed and direction (in degrees) averages. I need to convert it to 5 minute averages (that is the average from time 0-5min, 6-10min, 11-15 min and so on). I've attempted to use the text and indirect functions to do this, but it ends up over lapping (i get averages from 0-5, then 5-9, then 9-13). What kind of code or functions can I write to make this work? I assume your data of windspeeds is in B2:Bn, and that there is one entry per minute. To return the averages, stepping five each time, you could use a formula like: =AVERAGE(OFFSET($B$2,(ROWS($A$1:A1)-1)*5,0,5)) If you fill down, each subsequent entry will average the next five items. 1-5 6-10 11-15 etc. (The ROWS function is only to provide a counter, and has no relation to the storage of any of your data.) (If your data begins in a cell other than b2, merely change that one variable -- but be sure to use the absolute addressing mode). --ron |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Making 5 Minute Wind Averages
"Ron Rosenfeld" wrote: On Sat, 10 Nov 2007 11:22:01 -0800, Larissa wrote: Right now, I have data that is in 1 minute wind speed and direction (in degrees) averages. I need to convert it to 5 minute averages (that is the average from time 0-5min, 6-10min, 11-15 min and so on). I've attempted to use the text and indirect functions to do this, but it ends up over lapping (i get averages from 0-5, then 5-9, then 9-13). What kind of code or functions can I write to make this work? I assume your data of windspeeds is in B2:Bn, and that there is one entry per minute. To return the averages, stepping five each time, you could use a formula like: =AVERAGE(OFFSET($B$2,(ROWS($A$1:A1)-1)*5,0,5)) If you fill down, each subsequent entry will average the next five items. 1-5 6-10 11-15 etc. (The ROWS function is only to provide a counter, and has no relation to the storage of any of your data.) (If your data begins in a cell other than b2, merely change that one variable -- but be sure to use the absolute addressing mode). --ron You are amazing. Works perfectly. Thank you SO much!!! |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Making 5 Minute Wind Averages
On Sat, 10 Nov 2007 11:49:00 -0800, Larissa
wrote: You are amazing. Works perfectly. Thank you SO much!!! Glad to help. Thanks for the feedback. --ron |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
convert time from 60 minute hour to 100 minute hour | Excel Discussion (Misc queries) | |||
convert 100 minute hour to a 60 minute hour | Excel Worksheet Functions | |||
help with excel macros getting daily averages from 10 minute data | Excel Programming | |||
help with macros for getting daily averages from 10 minute increment data - MS2000 | Excel Programming | |||
help with macros for getting daily averages from 10 minute increment data - MS2000 | Excel Programming |