Absolute reference within a formula not really absolute

[Fred]

I'm using the AVERAGE function within a formula,
referencing columns to the right of the cell. There are
absolute column references within the function
(AVERAGE$I95:$AJ95)

But when I insert a new column to the left side of the
data section, that is insert a new column I, the column
references in the formula change to $J95:$AK95.

The absolute column reference within the formula isn't
holding the absolute reference.

Any ideas on a solution?

Thanks

 

[Juan Sanchez]

Fred

Absolutness of a formula due to $ means that it will
always refer to the same cell, if that cell moves because
of an insertion of some sort, the reference *will* change
to follow the new position of the cell...

if you don't want the reference to change no matter what
you can use something like this:

=AVERAGE(INDIRECT("I95:A95"))

Cheers
Juan

 

[Fred]

Worked like a charm! Thanks, Juan

-----Original Message-----
Fred

Absolutness of a formula due to $ means that it will
always refer to the same cell, if that cell moves because
of an insertion of some sort, the reference *will* change
to follow the new position of the cell...

if you don't want the reference to change no matter what
you can use something like this:

=AVERAGE(INDIRECT("I95:A95"))

Cheers
Juan

.

 

[Juan Sanchez]

Glad to help

Cheers
Juan

-----Original Message-----
Worked like a charm! Thanks, Juan
.