Prevent auto incrementation of formula

Marie Bayes · External Since: Jul 31, 2006 Posts: 42

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.

Billy Liddel · External Since: Nov 22, 2006 Posts: 199

"Marie Bayes" wrote:

> Does anyone know of a way to prevent formula from automatically updating
> themselves when new cells are added into a range.

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

Marie Bayes · External Since: Jul 31, 2006 Posts: 42

Absolutely Brilliant, thanks Billy it works a treat!

"Billy Liddel" wrote:

>
>
> "Marie Bayes" wrote:
>
> > Does anyone know of a way to prevent formula from automatically updating
> > themselves when new cells are added into a range.
>
> 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

Billy Liddel · External Since: Nov 22, 2006 Posts: 199

Marie

Thanks for the feedback and rating.

Peter

"Marie Bayes" wrote:

> Absolutely Brilliant, thanks Billy it works a treat!
>
> "Billy Liddel" wrote:
>
> >
> >
> > "Marie Bayes" wrote:
> >
> > > Does anyone know of a way to prevent formula from automatically updating
> > > themselves when new cells are added into a range.
> >
> > 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

Bernard Liengme · External Since: Jan 27, 2004 Posts: 2549

Here is an example, you can modify it for your formula
=AVERAGE(INDIRECT("A4:A23")
best wishes
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"Marie Bayes" <MarieBayes RemoveThis @discussions.microsoft.com> wrote in message
news:DEB05D81-06F3-4F6E-A431-27996576F3EA@microsoft.com...
> 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.

Marie Bayes · External Since: Jul 31, 2006 Posts: 42

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

"Bernard Liengme" wrote:

> Here is an example, you can modify it for your formula
> =AVERAGE(INDIRECT("A4:A23")
> best wishes
> --
> Bernard V Liengme
> www.stfx.ca/people/bliengme
> remove caps from email
>
> "Marie Bayes" <MarieBayes DeleteThis @discussions.microsoft.com> wrote in message
> news:DEB05D81-06F3-4F6E-A431-27996576F3EA@microsoft.com...
> > 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.
>
>
>