Mixed references

[damezumari]

In Excel 2003 Help it says:

"Mixed references A mixed reference has either an absolute column
and relative row, or absolute row and relative column. An absolute
column reference takes the form $A1, $B1, and so on. An absolute row
reference takes the form A$1, B$1, and so on. If the position of the
cell that contains the formula changes, the relative reference is
changed, and the absolute reference does not change."

= SUM(A$2:$B5) = SUM(B$2:$B5) = SUM($B$2:C5)
= SUM(A$2:$B6) = SUM(B$2:$B6) = SUM($B$2:C6)
= SUM(A$2:$B7) = SUM(B$2:$B7) = SUM($B$2:C7)
= SUM(A$2:$B8) = SUM(B$2:$B8) = SUM($B$2:C8)
= SUM(A$2:$B9) = SUM(B$2:$B9) = SUM($B$2:C9)

I copied the upper left cell to the other cells in the range above.
The range is C4:E9.

The first two columns gave the expected result, but for the third
column I had expected

= SUM(C$2:$B5)
= SUM(C$2:$B6)
= SUM(C$2:$B7)
= SUM(C$2:$B8)
= SUM(C$2:$B9)

Why did I get something else?

Regards,

Jan Nordgreen

 

[Daniel.C]

When you enter "=SUM(" and select B2:A4, the formula becomes :
=SUM(A2:B4)
In the third column, the formula would be :
=SUM(C$2:$B5)
which is translated :
=SUM($B$2:C5)
which is equivalent.
Regards.
Daniel

 

[damezumari]

I understand!

If you enter a range as (upper-right:lower-left) Excel automatically
converts it to (upper-left:lower-right).

Thank you for answering.

Regards,

Jan Nordgreen