Microsoft Knowledge Base
XL: Unexpected Results Using Intersection in Formulas
Last reviewed: August 12, 1997
Article ID: Q170228
The information in this article applies to:
- 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:
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.
- Select the range A1:B4.
- On the Insert menu, point to Name, and then click Create.
- Click OK.
In cell B6 enter the following formula:
=SUM(January Sales:January Cost2)
Note that the result in cell B6 is -300.
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
Additional query words: Last reviewed: August 12, 1997 |