Variable sized average macro/function

[danwtf2004]

Hi everyone,

I'm at a loss on how to do something, hopefully some guru here can
help.

I need to take a "variable-sized average" of data, and for the life of
me, I can't get it to work with VBA / macros, etc... The best way to
illustrate what I want to do is to describe what I am doing today, and
the shortcomings will become obvious.


- I have data in A1:A100 such as 1, 2, ...100
- I have data in B1:100 such as 10, 20, ... 1000
- I have a value in Z1 of 5
- Columns A and B are data
- Cell Z1 is the number of samples to average


Right now, in column D1, I have something like this:

D5 = AVERAGE(A5:A1)
D6 = AVERAGE(A6:A2)
D7 = AVERAGE(A7:A3)

 

[Tom Ogilvy]

=IF(ROW()<$Z$1,"",AVERAGE(INDIRECT("A"&ROW()&":A"&ROW()-$Z$1+1)))

You could also use the offset function

=IF(ROW()<$Z$1,"",AVERAGE(OFFSET(INDIRECT("A"&ROW()),-$Z$1+1,0,$Z$1,1)))

 

[danwtf2004]

Thanks Tom! That is working great.

I appreciate the help very much.

da