Prevent auto incrementation of formula

M

Marie Bayes

Does anyone know of a way to prevent formula from automatically updating
themselves when new cells are added into a range.
I'm trying to base a calculation on the first 20 cells in a range of cells,
additional cells will be inserted at the start of this range, but I don't
want the formula to update the range past the first 20 cells. (I'm getting
an average of the 20 latest figures to be entered, if that helps). Thanks in
advance.
 
B

Billy Liddel

Marie Bayes said:
Does anyone know of a way to prevent formula from automatically updating
themselves when new cells are added into a range.
Click to expand...

Marie

In a blank cell type the range you want to sum e.g. A1:A20 Then enter the
formula
=SUM(INDIRECT(C1)) where C1 is the cell in which you entered the range to be
summed.

You can change the font colour to white so that it is hidden and not shown
in a print.

Regards
Peter
 
B

Bernard Liengme

Here is an example, you can modify it for your formula
=AVERAGE(INDIRECT("A4:A23")
best wishes
 
M

Marie Bayes

Thanks Bernard, this one worked a treat too...

Bernard Liengme said:
Here is an example, you can modify it for your formula
=AVERAGE(INDIRECT("A4:A23")
best wishes
Click to expand...
 
Top