Question

Locked

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.

2 total posts (Page 1 of 1)  
| 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 ...

then remove '

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:
=AVERAGE(OFFSET(B1,2,0,30))

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.

Back to Software Forum
2 total posts (Page 1 of 1)  

Related Discussions

Related Forums