Making cell references super-absolute

  • Thread starter Thread starter misterhanky
  • Start date Start date
M

misterhanky

How do I make a cell reference so permanent that even inserting rows or
columns above, below, or within the ranges of the formula does not
affect the reference?

In other words, "=AVERAGE($E$7:$E$11)" is good and well, until I insert
a row of cells in row 7, and then Excel changes my formula to
"=AVERAGE($E$8:$E$12)". I don't want Excel to change my formula.

I'll also be happy to consider other workarounds-- but the people who
will be using this template are technology-illiterate.

Thanks.
 
You are aware, aren't you, that the super-absolute formula no longer applies
to the original cells after a new row 7 is inserted?

Well, nonetheless, you can use

=AVERAGE(INDIRECT("$E$7:$E$11"))

which is immune to row insertion or deletion, but opens the door to
maintenance issues. Hard-coding addresses as text is rarely a good idea.

You can also use

=AVERAGE(OFFSET(E1,6,0,5,1))

but this can be affected if somebody inserts a new row 1
 
Back
Top