The information in this article applies to:
SYMPTOMS
In Microsoft Excel, if you enter a formula in a range of cells by
selecting the cells, typing the formula, and pressing CTRL+ENTER, cell
references in the formulas may be switched to absolute instead of relative
referencing or vice versa. Also, in a cell reference, designations for two
cells may switch rows or columns (for example, the range B7:D5 may appear
as B5:D7).
CAUSE
These problems may occur when you apply the formula to all of the
cells and the formula contains a "backward" reference to a range of cells.
A backward reference is one in which the second cell in the reference is
either above or to the left of the first cell.
WORKAROUND
To work around this problem, use the same combination of absolute and
relative referencing for both cells that are referenced in the formula.
$B$5:$B$10Any other combinations that use a mixture of absolute and relative referencing (for example, B5:$B$10) may cause the problem to occur. STATUSMicrosoft has confirmed this to be a problem in the Microsoft products listed at the beginning of this article. MORE INFORMATIONThe following examples demonstrate the problems described in the "Symptoms" section. Example 1In a new worksheet, select the range A1:A2, type the following formula
and then press CTRL+ENTER.
Note the information about cells A1 and A2 that is listed in the following table.
=When the formula is applied to cell A2, the second cell, B1, is above the
first cell, B2. When Microsoft Excel switches the two cells, it incorrectly
applies absolute and relative referencing to the formula. However, the
formula does refer to the correct cells.
Note that if you enter the following formula instead
both of the formulas in cells A1 and A2 are correct.
Example 2In a new worksheet, select the range A1:C3, type the following formula
and then press CTRL+ENTER.
Note the information about cells A1:C3 that is listed in the following table.
When the formula is applied to cells A3, B3, C1, C2, and C3, the second
cell in each reference is above or to the left of the first cell. When
Microsoft Excel switches the cells, it incorrectly applies absolute and
relative referencing to the formula.
Note that cells in the formulas in cells A3 and C1 have also switched row numbers; instead of cell F4, the formulas in A3 and C1 refer to cells F5 and F3. The ranges that are referenced by the formulas are correct even though the row numbers are switched. Note also that the formula in cell C3 appears correctly even though the second cell, F4, is both above and to the left of the first cell, G5. Additional query words: XL97
Keywords : xlformula |
Last Reviewed: October 30, 1999 © 2000 Microsoft Corporation. All rights reserved. Terms of Use. |