fixing formula

[namsilat]

Imagine I have a spread sheet with 10 numbers in cells A1 to A10, and I have a formula lined up at B1 to calculate average of these 10 numbers. I then insert a row (say between A5 and A6), the formula at B1 now calculates average of 11 numbers in A1 to A11. How can I change the formula in B1 to "fix" it, such that it continues to calculate average of just first 10 numbers from A1 to A10, and not affected by inserting or deleting rows? I thought about using AVERAGE($A$1:$A$10), but if I copy the the formula to B2, it would just calculate average of A1 to A10 instead of A2 to A11.

 

[Marcelo Branco]

Hi,

Try this

=AVERAGE(INDIRECT("A1:A10"))

HTH

M.

 

[Marcelo Branco]

Sorry, i misread your post.

Maybe creating a named range, say MyRange, with the formula in Refers To
=INDIRECT("Sheet1!$A$1:$A$10")

and in B1
=AVERAGE(OFFSET(MyRange,ROW()-1,0))
copy down

M.