Variable averaging index in Excel ?

[walker2]

I am trying to simulate a filter/regulation in Excel.

For that I would like to be able to specify an averaging length/time as a parameter. I.e. be able to average from a specified cell to a cell a specified number of cells further up the collumn (back in time).

Something like =average(A[row]:A[row-n]) where n is a specified but changeble number taken from some cell in the sheet and the the bracets [] simply are used to indicate the complete wanted row indexes for the averaging of collumn A.
I.e. In case of a fixed, never to be changed n=30, I could instead have written "=average(A100:A70)" for cell B100, followed by "=average(A101:A71)" for cell B101

Maybe using INDEX function is the key to this indirect indexing, but so far I have not figured out how to use it indirectly.

Any ideas?

 

[AnotherHiggins]

In B100, type [COLOR=blue white]=average(A100:A70)[/color]

Copy the cell.

Paste it into B101.

Voila!

Have a look at "Relative References" and "Absolute References" in Excel's Help.

NOTE:
An alternative way to increment the references:
*Hover the cursor over the lower right-hand corner of B100 until it changes to a bold plus sign (+)

*click and drag down.

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.

 

[combo]

You can: =AVERAGE(OFFSET(A1,0,0,10,1)), where numbers can be replaced by parameters (references).

combo

 

[walker2]

Yes!!

Offset() did the trick!

=average(A100: offset(A100;-n$3;0))
averages cells A[100-[n$8]] to A[100]

(I hope I have written it correctly But the algoritm works in my sheet)


Thanks.