Locking Formulas

Hello,

I need to average 3 cells. However, whenever I add a new column, the formulas shift by a cell, but I dont want them to. How do I keep the formulas from changing?

Example:

Avg Cell F Cell G Cell H

Now, I want to Avg Cell F-H

However, when I insert a new cell to put this latest years numbers in I want what is currently in Cell H (Soon to be Cell I when I insert a column at Cell F) to drop out of the equation. I still want to Avg Cell F-H but Excel keeps shifting the formula to Cell G-I.

Ok, hope that makes sense. How do I lock the formula?

Hi!

Try this:

=AVERAGE(INDIRECT("F1:H1"))

That formula will always refer to cells F1:H1.

Biff

"Thadar" <[email protected]> wrote in
message news:[email protected]...
>
> Hello,
>
> I need to average 3 cells. However, whenever I add a new column, the
> formulas shift by a cell, but I dont want them to. How do I keep the
> formulas from changing?
>
> Example:
>
> Avg Cell F Cell G Cell H
>
> Now, I want to Avg Cell F-H
>
> However, when I insert a new cell to put this latest years numbers in I
> want what is currently in Cell H (Soon to be Cell I when I insert a
> column at Cell F) to drop out of the equation. I still want to Avg
> Cell F-H but Excel keeps shifting the formula to Cell G-I.
>
> Ok, hope that makes sense. How do I lock the formula?
>
>
> --
> Thadar
> ------------------------------------------------------------------------
> Thadar's Profile:
> http://www.excelforum.com/member.php...o&userid=22506
> View this thread: http://www.excelforum.com/showthread...hreadid=494242
>

"Biff" wrote:
> .. =AVERAGE(INDIRECT("F1:H1"))

If we need to copy down from the starting cell,
one variation on Biff's suggestion above to try in say, K1:
=AVERAGE(OFFSET(INDIRECT("F1"),ROW(A1)-1,,,3))
K1 could then be copied down to return correspondingly
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--

Another variant, placed in K1, copied down:
=AVERAGE(OFFSET(INDIRECT("F1:H1"),ROW(A1)-1,))
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--