Keeping Formulas the same when inserting columns

[Guest]

I have a spreadsheet where colum B is =AVERAGE(C2:H2) , which is the average
of 6 weeks of data. Every week I'll insert a new column at C. I want the
formula in column B to always be what is above, however, when I insert a new
column Excel thinks for itself and changes the formual in B to
=AVERAGE(D2:I2).

Does anyone know how to keep the formula to always read the cell reference
that is listed in there?

 

[Guest]

Hi,

One way is to use the indirect function:

=AVERAGE(INDIRECT("C2:H2"))

or if you need to copy down

=AVERAGE(INDIRECT("C"&ROW(A2)&":H"&ROW(A2)))

HTH
Jean-Guy

 

[DaveO]

Because you're doing this every week, you can automate the entire
process with a macro. As part of that macro, you might convert the
=AVERAGE(C2:H2) formula to a text string by replacing the = sign with
your initials, inserting the column, and then replacing your initials
with the = sign to convert it back to a dynamic formula. This method
works when there is a range of formulas to change. If there's only one
formula you might use this line in your macro:
Range("B15").formula = "=AVERAGE(C2:H2)"
(You'll need to change the B15 reference to the proper cell address,
of course.)