Making 5 Minute Wind Averages

L

Larissa

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?
 
R

Ron Rosenfeld

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
 
L

Larissa

Ron Rosenfeld said:
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!!!
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads


Top