Error copying cell containing 'IF' function and labels

[David]

I'm running Excel 97 SR-2 on Windows XP (and have found the same problem
running Excel 97 on Win95). When copying a cell containing an 'IF' function
that references a column label, the reference is an absolute reference rather
than a relative reference. For copying other cells with formulae containing
column labels but not 'IF' functions, the column label reference is a
relative reference as is the obvious intent when using labels. After pasting
in the destination, the absolute reference is changed to relative if the
contents of the destination cell is simply copied from itself to itself.
After this, the label in this destination cell is treated as a relative
reference when the cell is copied and pasted.

A succinct statement of the error in Excel is that labels in IF functions
are treated as absolute references whereas they should be treated as relative
references. It is changed to a relative reference if the formula is
re-entered. It is egregious that one can't look at the formula and know
whether the reference is relative or absolute. Audit's "Trace Precedents" can
be used to assist in debug.

Example.
Enter the following table.

input1 inter1 output1
3.03 =input1/2 =IF(input1>2,inter1,input1)
1.02

Now copy the first row of columns inter1 and output1 to the second row. and
the results will be the following.

input1 inter1 output1
3.03 1.515 1.515
1.02 0.51 3.03

If the column label reference were treated as relative rather than absolute,
the table would be the following.

input1 inter1 output1
3.03 1.515 1.515
1.02 0.51 1.02

Excel correctly treats the label refence in the IF statement as relative if
re-evaluation of the first cell in the output1 column is forced by copying
and pasting the formula to the same cell as through the following keystrokes.
1. Select the first cell in the output1 column.
2. <F2> <shift-Home> <ctrl-c> <esc> <ctrl-v>

Now copy the first cell in the output1 column to the second cell in the
output 1 column and see that the label reference was treated as relative, so
that now the table will appear as the following. (The change is to the value
of output1 in the second row.)

input1 inter1 output1
3.03 1.515 1.515
1.02 0.51 1.02

Is there a way to force the re-evaluation of these cells for an entire
worksheet so that the label reference are treated as relative rather than
absolute? Is there another way to handle this problem rather than to not use
labels in IF statements? My intuition is to not use labels at all if what
they reference can be "indeterminate" like this.

Thanks in advance for your help.

 

[Dave D-C]

.. succinct statement .. It is egregious that ..
Very interesting. I have never used column labels.

1. Select the first cell in the output1 column.
2. <F2> <shift-Home> <ctrl-c> <esc> <ctrl-v>

A simpler demonstration:
1. Select the second cell in the output1 column.
2. <F2> <Enter>
and the 3.03 becomes 1.02

The XL97 help (labels,in formulas / Learn about labels ..)
has a Note:
"By default, names use absolute cell references."
This must apply to labels also.

I'll be looking for more comments. Dave D-C

 

[David]

Very interesting. I have never used column labels.

A simpler demonstration:
1. Select the second cell in the output1 column.
2. <F2> <Enter>
and the 3.03 becomes 1.02

The XL97 help (labels,in formulas / Learn about labels ..)
has a Note:
"By default, names use absolute cell references."
This must apply to labels also.

Thanks very kindly for your input, but I don't think so. That's the
difference between a "name" and a "label". A name references a specific cell,
but a column label defines a specific column but relative row. A key point
I"m making is that column references seem to be treated differently inside
"IF" functions. I haven't seen the problem with other functions or formulas
that don't contain the"IF" function.

My current work-around is probably to not use column labels in "IF"
functions, but I suspect there's something critical here that I don't know -
which is why I'm at this forum. I need help.
<deletions>