Do not update formula when inserting rows in Excel

By 7s1u7le79ba7089 ·
In Excel, I want to be able to insert a row but NOT update a formula referencing that row. For example in Cell B1 I have the formula =AVERAGE(B3:B33) now when I insert a new row at row 3 the formula automatically changes to =AVERAGE(B4:B34) I want to ALWAYS average the rows between 3 and 33.
I have tried absolute values in the formula =AVERAGE($B$3:$B$33) but even they change to =AVERAGE($B$4:$B$34)when I insert a new row.
Any ideas?

This conversation is currently closed to new comments.

Thread display: Collapse - | Expand +

All Answers

Collapse -

=AVERAGE(B3:B33) --> '=AVERAGE(B3:B33), then insert row

by Absolutely In reply to Do not update formula whe ...
Collapse -

I've found the answer

by 7s1u7le79ba7089 In reply to =AVERAGE(B3:B33) --> '=AV ...

By using the Offset formula within the Average formula I am able to insert rows without changing the area I need calculating.
Here is how I wrote the formula:

This formula averages 30 rows starting 2 rows below cell B1 and zero columns over.
I've tested the formula and it works exactly the way I need it to.

Related Discussions

Related Forums