Locking Formulas

T

Thadar

Hello,

I need to average 3 cells. However, whenever I add a new column, the
formulas shift by a cell, but I dont want them to. How do I keep the
formulas from changing?

Example:

Avg Cell F Cell G Cell H

Now, I want to Avg Cell F-H

However, when I insert a new cell to put this latest years numbers in I
want what is currently in Cell H (Soon to be Cell I when I insert a
column at Cell F) to drop out of the equation. I still want to Avg
Cell F-H but Excel keeps shifting the formula to Cell G-I.

Ok, hope that makes sense. How do I lock the formula?
 
B

Biff

Hi!

Try this:

=AVERAGE(INDIRECT("F1:H1"))

That formula will always refer to cells F1:H1.

Biff
 
M

Max

Biff said:
.. =AVERAGE(INDIRECT("F1:H1"))
Click to expand...

If we need to copy down from the starting cell,
one variation on Biff's suggestion above to try in say, K1:
=AVERAGE(OFFSET(INDIRECT("F1"),ROW(A1)-1,,,3))
K1 could then be copied down to return correspondingly
 
M

Max

Another variant, placed in K1, copied down:
=AVERAGE(OFFSET(INDIRECT("F1:H1"),ROW(A1)-1,))
 
Top