Excel is fooling me

[kurt]

As I'm using large excel sheets, I must be able to rely on excel,
whenever I insert or add data, to calculate without making mistakes.
However I've encountered the folowing problem:
I have following formulas in the cells:
H72 = ROW()-1
I72 = ROW($L$174)-H72
H73 = ROW()-1
I73 = ROW($L$174)-H73
The results shown on screen are
H72 = 71 -> CORRECT
I72 = 102 -> WRONG (174-71=103)!!!!
H73 = 72 -> CORRECT
I73 = 102 -> CORRECT (174-72=102)
When I check the calculation of cell I72
(Menu>Tools>Formula Auditing>Evaluate)
It says : ROW($L$174) equals 174 -> Correct
H72 equals 71 -> Correct
Substraction equals 102 -> WRONG !!!

I encounter this problem freguently after inserting
one or more lines with the "insert copied lines" command.
I have to use that command to avoid re-entering all formulas
on the inserted lines.] A few lines above the inserted row(s)
the problem starts and persists till the top row.
The weird thing is that it doesn't always happen,
sometimes it just goes OK.

Copying the formula from I73 to the top solves the problem
but the next time I insert lines it can happen again (or not).
You see the problem is I have to check every time to be sure
because there are still other formulas using these values as input.
It isn't a display problem either because further calculating
always uses the shown (wrong) value.

Who has an idea?

For your information:
It happens on different computers - different operatingsystems
and different versions of Excel (e.g. 2002).
Turning on/off 'automatic calculation' doesn't help.

 

[Bob Phillips]

Kurt,

Something is fooling you, but there is nothing wrong in Excel. I just
entered those formulae, and they all calculated correctly.

IMO, it must be something you are doing when inserting rows. You may be
thinking that because you use absolute formulae, they stay absolute. They
stay absolute, but they doesn't mean they do not change. If I72 contains =
ROW($L$174)-H72, and then you insert a row at row 100 say, I72 will then
contain = ROW($L$175)-H72.

Go through your steps one by one, checking the formulae at each step, and
see when it changes.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Aladin Akyurek]

If you are inserting rows before the current data, =ROW()-1 in H72 will not
equal 72-1, that is, 71.

 

[kurt]

Thanks for this answer, but it doesn't help me yet.
I know this works perfectly on a new blank worksheet.
To prove what I'm saying, I will E mail you the file.

I still think something is wrong with excel...

Kurt,

Something is fooling you, but there is nothing wrong in Excel. I just
entered those formulae, and they all calculated correctly.

IMO, it must be something you are doing when inserting rows. You may be
thinking that because you use absolute formulae, they stay absolute. They
stay absolute, but they doesn't mean they do not change. If I72 contains =
ROW($L$174)-H72, and then you insert a row at row 100 say, I72 will then
contain = ROW($L$175)-H72.

Go through your steps one by one, checking the formulae at each step, and
see when it changes.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

As I'm using large excel sheets, I must be able to rely on excel,
whenever I insert or add data, to calculate without making mistakes.
However I've encountered the folowing problem:
I have following formulas in the cells:
H72 = ROW()-1
I72 = ROW($L$174)-H72
H73 = ROW()-1
I73 = ROW($L$174)-H73
The results shown on screen are
H72 = 71 -> CORRECT
I72 = 102 -> WRONG (174-71=103)!!!!
H73 = 72 -> CORRECT
I73 = 102 -> CORRECT (174-72=102)
When I check the calculation of cell I72
(Menu>Tools>Formula Auditing>Evaluate)
It says : ROW($L$174) equals 174 -> Correct
H72 equals 71 -> Correct
Substraction equals 102 -> WRONG !!!

I encounter this problem freguently after inserting
one or more lines with the "insert copied lines" command.
I have to use that command to avoid re-entering all formulas
on the inserted lines.] A few lines above the inserted row(s)
the problem starts and persists till the top row.
The weird thing is that it doesn't always happen,
sometimes it just goes OK.

Copying the formula from I73 to the top solves the problem
but the next time I insert lines it can happen again (or not).
You see the problem is I have to check every time to be sure
because there are still other formulas using these values as input.
It isn't a display problem either because further calculating
always uses the shown (wrong) value.

Who has an idea?

For your information:
It happens on different computers - different operatingsystems
and different versions of Excel (e.g. 2002).
Turning on/off 'automatic calculation' doesn't help.