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?

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

"rhonda stringfellow" wrote:

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?

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.)