Formula Adjustment when inserting a column

Posted by Spence Baker on January 18, 2001 6:56 AM

If Cell A1 has an average of B1,C1,D1 and E1; and I
insert a new column at column B, the the formula in cell A1
changes to be an average of cell C1,D1,E1 and F1.

Is there any way to lock that formula down so that when I
insert a new column, it does not adjust?

Thanks again,

Spence

Posted by Aladin Akyurek on January 18, 2001 7:22 AM

Is this what you want:

A1 =AVERAGE(OFFSET($A$1;0;1;2;4))

Aladin

Posted by Bruce on January 18, 2001 8:42 AM

You could always make sure you insert the column inside of the "anchor" columns to make the formula still work.

Posted by Mark W. on January 18, 2001 1:30 PM

Make that:

=AVERAGE(OFFSET(A1,,1,,4))

Posted by Ben on January 19, 2001 11:19 AM

Wonderful!

I know exactly what Spence is trying to do. I've been beating my head over the same type of problem, i.e. how to insert rows without disturbing the formulas. The OFFSET function works great. Thanks.