CODEWITHSUNDEEP
excelexcel-formula
Jasper
Jasper

Reputation: 137

How to keep formula to use the same reference when inserting cells?

I have an worksheet with several formulas. Actually fairly simple ones, with SUM, AVERAGE, MAX and MIN and so on. However, when new data comes in, I need to insert the new row at the top. The AVERAGE, MIN and MAX cells are above the first data entry. So, when I insert a row the MIN & MAX functions keep row 13 as their first row, that's good. However, the AVERAGE function, changes to the next row... I don't get anything working to keep the first 12 periods as average (starting row 13)

I tried making the column a table -> no luck I tried $ reference (AVERAGE($C$13:$C$24) -> no luck I tried include OFFSET, but the OFFSET itself will be changed to C14 when inserting the row -> no luck

I expect all my formulas to keep the original range when inserting rows

BUT! when the MAX function is used, it should be the MAX of all the values, including the new value...to make it worse ;-)

Upvotes: 0

Views: 594

Answers (1)

JvdV
JvdV

Reputation: 75990

For example:

=AVERAGE(INDEX($C:$C,13):INDEX($C:$C,24))

would evaluate to =AVERAGE($C$13:$C$24) and won't increase/decrease in size, while:

=MAX(INDEX($C:$C,13):$C24)

would increase in range when adding a new row but will start at C13.

Upvotes: 2

Related Questions