XL: Formula References Incorrect After Pressing CTRL+ENTER

ID: Q172791

The information in this article applies to:

Microsoft Excel 97 for Windows
Microsoft Excel for Windows 95, versions 7.0, 7.0a
Microsoft Excel for the Macintosh, versions 5.0, 5.0a
Microsoft Excel for Windows, versions 5.0, 5.0c

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).

This may cause problems if you then fill the formulas or if you insert or delete rows or columns in a worksheet.

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.

NOTE: If you are not using the same combination of absolute and relative cell referencing for both cells in the formula, the problem becomes apparent when you fill the formulas or enter them in a range of cells by pressing CTRL+ENTER.

WORKAROUND

To work around this problem, use the same combination of absolute and relative referencing for both cells that are referenced in the formula.

For example, the following combinations of absolute and relative referencing do not cause the problem to occur:

   $B$5:$B$10
   B5:B10
   B$5:B$10
   $B5:$B10

Any other combinations that use a mixture of absolute and relative referencing (for example, B5:$B$10) may cause the problem to occur.

STATUS

Microsoft has confirmed this to be a problem in the Microsoft products listed at the beginning of this article.

MORE INFORMATION

The following examples demonstrate the problems described in the "Symptoms" section.

Example 1

In a new worksheet, select the range A1:A2, type the following formula

   =SUM(B1:$B$1)

and then press CTRL+ENTER.

Note the information about cells A1 and A2 that is listed in the following table.

                                             Referencing
          Expected        Formula that       correct or
   Cell   formula         actually appears   incorrect
   ----------------------------------------------------

   A1     =SUM(B1:$B$1)   =SUM(B1:$B$1)      Correct
   A2     =SUM(B2:$B$1)   =SUM(B$1:$B2)      Incorrect

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

   =SUM($B$1:B1)

both of the formulas in cells A1 and A2 are correct.

Example 2

In a new worksheet, select the range A1:C3, type the following formula

   =SUM(E3:$F$4)

and then press CTRL+ENTER.

Note the information about cells A1:C3 that is listed in the following table.

                                             Referencing
          Expected        Formula that       correct or
   Cell   formula         actually appears   incorrect
   ----------------------------------------------------

   A1     =SUM(E3:$F$4)   =SUM(E3:$F$4)      Correct
   A2     =SUM(E4:$F$4)   =SUM(E4:$F$4)      Correct
   B1     =SUM(F3:$F$4)   =SUM(F3:$F$4)      Correct
   B2     =SUM(F4:$F$4)   =SUM(F4:$F$4)      Correct
   C3     =SUM(G5:$F$4)   =SUM($F$4:G5)      Correct

   B3     =SUM(F5:$F$4)   =SUM(F$4:$F5)      Incorrect
   C2     =SUM(G4:$F$4)   =SUM($F4:G$4)      Incorrect
   A3     =SUM(E5:$F$4)   =SUM(E$4:$F5)      Incorrect
   C1     =SUM(G3:$F$4)   =SUM($F3:G$4)      Incorrect

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 
Version           : WINDOWS:97,7.0,5.0; MACINTOSH:5.0
Platform          : MACINTOSH WINDOWS
Issue type        : kbprb

Last Reviewed: January 8, 1999