At 06:55 08/06/2010 -0400, William Drescher wrote:
I am computing a moving average and comparing it to a current value
(looking at the current weights of dogs compared to their average
over the prior 3 months).
So my cell formula is (in column I):
=h2 - ((e2 + f2 + g2)/3)
Each month I insert a new column just before the average column,
then I need to redo this formula.
How can I write the formula so that it uses
= 1_cell_to_the_left - (4_cells_to_the_left + 3_cells_to_the_left +
2_cell_to_the_left)/3)
Taking your example of the result being in cell I2, enter:
=OFFSET(I2;0;-1)-AVERAGE(OFFSET(I2;0;-4):OFFSET(I2;0;-2))
The OFFSET() function looks at a cell with reference to a given
cell. In this case we are referring everything to the result
cell. The "0" argument says that we want to use the same row, the
"-1" specifies the cell to the left, and "-4" and "-2" the range for
the three cells to be averaged. Note that you can copy or fill this
formula down a column in the normal way. If you insert a new column
I, the Is in the formula will become Js in the new column J. And so on.
I trust this helps.
Brian Barker
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
