Software

Ask something
Question Locked

Do not update formula when inserting rows in Excel

0 helpful
3 replies

by 7s1u7le79ba7089 2 views

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

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?

3 replies

by 7s1u7le79ba7089

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.

This discussion is closed to new replies.