Microsoft Knowledge Base

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

SYMPTOMS

In Microsoft Excel, if you enter a formula with an intersection by using either defined names or natural language formulas, you may receive incorrect results.

CAUSE

This behavior is caused by the order in which operations are performed for formulas in Microsoft Excel. In Microsoft Excel, the range operator (a colon) precedes the intersection operator (a space) in the order of operations.

WORKAROUND

To work around this behavior, use parenthesis to enclose the intersection. For example, follow these steps:

1. In a new worksheet, type the following data:

         A1:          B1:   January
         A2: Sales    B2:   500
         A3: Cost1    B2:  -100
         A4: Cost2    B4:  -300
   
      NOTE: If you are using Microsoft Excel 97, skip to step 5.
   
   

2. Select the range A1:B4.

3. On the Insert menu, point to Name, and then click Create.

4. Click OK.

5. In cell B6 enter the following formula:

         =SUM(January Sales:January Cost2)
   

   Note that the result in cell B6 is -300.

6. To receive the expected result change the formula in B6 to the following:

         =SUM((January Sales):(January Cost2))
   

   The result in cell B6 is 100.

STATUS

This behavior is by design in the Microsoft products listed at the beginning of this article.

MORE INFORMATION

For additional information, please see the following articles in the Microsoft Knowledge Base:

   ARTICLE-ID: Q25189
   TITLE     : XL: Order of Operations in Formulas

   ARTICLE-ID: Q61891
   TITLE     : Using Intersections of Named Ranges in Excel Formulas

   ARTICLE-ID: Q161881
   TITLE     : XL97: How to Use Natural Language Formulas