Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel 97 error copying cell containing 'IF' function and labels
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(input12,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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
This is a re-post with extra newlines (hopefully) removed.
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(input12,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. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Minor correction with extra newlines deleted.
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(input12,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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
#REF! error when copying formula beyond row 65536 in Excel 2007 | Excel Discussion (Misc queries) | |||
Copying labels down | Excel Discussion (Misc queries) | |||
Copying Labels in Subtotals | Excel Discussion (Misc queries) | |||
copying the function contained within a cell to anouther cell. | Excel Worksheet Functions | |||
Excel Error when copying formulas | Excel Worksheet Functions |