Microsoft KB Archive/170228: Difference between revisions
(importing KB archive) |
m (Text replacement - """ to """) |
||
Line 72: | Line 72: | ||
</span><br /> | </span><br /> | ||
<br /> | <br /> | ||
THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED | 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.</blockquote> | ||
<span>Last reviewed: August 12, 1997</span><br /> | <span>Last reviewed: August 12, 1997</span><br /> | ||
[[../cpyright|©1997 Microsoft Corporation. All rights reserved. Legal Notices]].<br /> | [[../cpyright|©1997 Microsoft Corporation. All rights reserved. Legal Notices]].<br /> | ||
|} | |} |
Latest revision as of 11:04, 21 July 2020
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 |