Microsoft Knowledge Base

Formula with Division Operators Returns Incorrect Result
Last reviewed: March 27, 1997
Article ID: Q135092
5.00 5.00c 7.00 | 5.00 5.00a 
WINDOWS         | MACINTOSH
kbprb

The information in this article applies to:

  • Microsoft Excel for Windows, versions 5.0, 5.0c
  • Microsoft Excel for the Macintosh, versions 5.0, 5.0a
  • Microsoft Excel for Windows NT, version 5.0
  • Microsoft Excel for Windows 95, version 7.0

SYMPTOMS

In Microsoft Excel, you can enter a formula that is not preceded with an equal sign by doing either of the following:

  • Select the Transition Formula Entry check box (on the Transition tab of the Options dialog box)

    -or-

  • Precede the formula with a plus sign or a minus sign.

However, if you use either of these methods of entry with a formula that contains more than one division operator, the formula may return incorrect results.

CAUSE

This problem may occur if the cell is formatted with a number format other than General.

WORKAROUND

To work around this problem, use either of the following methods:

Method 1: Precede formulas with an equal sign.

Method 2: Format the cells with the General number format.

STATUS

Microsoft has confirmed this to be a problem in the Microsoft products listed at the beginning of this article. We are researching this problem and will post new information here in the Microsoft Knowledge Base as it becomes available.

MORE INFORMATION

The result is wrong because the formula is incorrectly evaluated. For example, if you were to enter the formula x/y/z, the formula is evaluated as x/(y/z) instead of being evaluated from left to right as expected. For example, with Transition Formula Entry turned on, if you were to enter the formula: 

   6000000/30/17
(without the equal sign) into a cell that was formatted with a number format other than General, the formula is incorrectly evaluated and returns the value 3400000. The formula is incorrectly evaluated as 

   6000000/(30/17)
and the resulting formula is 6000000/1.7647.

If the cell is formatted as General or if the formula is preceded with an equal sign, the formula returns the value 11764.71.

KBCategory: kbprb
KBSubcategory:

Additional reference words: 7.00 5.00 5.00c
Keywords : kbprb
Version : 5.00 5.00c 7.00 | 5.00 5.00a
Platform : MACINTOSH WINDOWS

THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

Last reviewed: March 27, 1997
©1997 Microsoft Corporation. All rights reserved. Legal Notices.