Excel 97 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-p>

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.

 

[David]

Problem Description:
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-p>

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.

 

[David]

The following is a repeat of the original post with the obvious <ctrl-p> typo
changed to <ctrl-v> and extra newlines deleted.

Problem Description:
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.