Absolute cell reference acts like relative

[Top Spin]

I guess I don't understand absolute and relative cell references.

I have a cell containing the following formula:

=AVERAGE(C$13:C23)

From what I read about cell references, the "C23" means that the
average range will end on C23, but that if that cell is moved, the
address will be updated to follow. Whereas, the "$13" means that the
average function will always start on row 13 even if cell C13 is
moved.

I have a table of values each stored in a row. When I add a new row, I
insert it just above row 13 so that row 13 becomes row 14. When I do
that, the above formula changes to:

=AVERAGE(C$14:C24)

It was my intention that is change to

=AVERAGE(C$13:C24)

What am I doing wrong?

Thanks

--
PC: HP Omnibook 6000
OS: Win 2K SP-4 (5.00.2195)
LAN: P2P with an HP Vectra workstation
Email: Usenet-20031220 at spamex.com
(11/03/04)

 

[Max]

Try : =AVERAGE(INDIRECT("C13:C"&ROW(C23)))

This'll lock C13 as the start cell. The ending cell will increase
automatically (i.e. range will expand) as you insert rows.

 

[Max]

Try : =AVERAGE(INDIRECT("C13:C"&ROW(C23)))

Missed out this clarification line:
The formula above is functionally equivalent to : =AVERAGE(C$13:C23)

 

[Top Spin]

Missed out this clarification line:
The formula above is functionally equivalent to : =AVERAGE(C$13:C23)

Can you tell me why my formula didn't work? Especially if it is, as
you say, "functionally equivalent"?

Thanks

--
PC: HP Omnibook 6000
OS: Win 2K SP-4 (5.00.2195)
LAN: P2P with an HP Vectra workstation
Email: Usenet-20031220 at spamex.com
(11/03/04)

 

[Ken Wright]

Because you inserted a row above the reference Excel will adjust the formula in
line with that. If you copied the cell and pasted elsewhere then the absolute
nature of the formula would prevenet Excel from changing it, but in this case
you are changing the structure of the worksheet, and probably in the bulk of
cases you would want the reference to move also. For those instances where you
don't want that, the only way to constantly refer to to a specific cell address
no matter what is to use INDIRECT which takes a Textual reference that is not
affected by any movement of cells etc.

 

[Max]

Good explanation, Ken !
Thanks ..