fixing formula

namsilat

New Member
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.

Last edited:

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},\$Z\$1:\$Z\$99,\$Y\$1:\$Y\$99),2,False) to lookup Y values to left of Z values.

Marcelo Branco

MrExcel MVP
Hi,

Try this

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

HTH

M.

Marcelo Branco

MrExcel MVP

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.

Last edited:

Replies
2
Views
135
Replies
16
Views
335
Replies
25
Views
699
Replies
0
Views
206
Replies
3
Views
183

1,190,581
Messages
5,981,773
Members
439,734
Latest member
hmopheim

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back