Variable range defenition

[john.shanks]

I have a large database from which I need to calulate a significan
number of "moving averages" of "variable" length. As a simpl
example, consider a sequence of numbers in cells A1:A100. To calculat
a moving average of length 10, I can put =AVERAGE(A1:A10) into cell B
and then copy down to B91. I now wish to change this moving averag
sequence to be based on a different length, say 15. Is there somewa
to place the required length into a separate cell which I can edit a
required and have the calculation update automatically without havin
to edit the B column manuly each time, ie can I somehow incorporate
variable into the range definition within the AVERAGE function

 

[Dave Peterson]

Something like:

=AVERAGE(A1:OFFSET(A1,$C$1-1,0))

If C1 held 10, you'd get the same as =average(a1:a10)

 

[RagDyeR]

Put your variable in K1, less 1, so that
K1 should contain 9 to have A1:A10.

=AVERAGE(A1:INDIRECT("A"&ROW()+$K$1))
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================


I have a large database from which I need to calulate a significant
number of "moving averages" of "variable" length. As a simple
example, consider a sequence of numbers in cells A1:A100. To calculate
a moving average of length 10, I can put =AVERAGE(A1:A10) into cell B1
and then copy down to B91. I now wish to change this moving average
sequence to be based on a different length, say 15. Is there someway
to place the required length into a separate cell which I can edit as
required and have the calculation update automatically without having
to edit the B column manuly each time, ie can I somehow incorporate a
variable into the range definition within the AVERAGE function ?

 

[Peo Sjoblom]

Or with less typing

=AVERAGE(OFFSET(A1,,,$C$1))

 

[Dave Peterson]

I'm usually happy when I get =offset() to work at all!

Or with less typing

=AVERAGE(OFFSET(A1,,,$C$1))

 

[john.shanks]

All, The variouos suggestions using OFFSET & INDIRECT fuctions will sav
me a lot of time. Many thanks for the help !

Regards
Joh