Microsoft KB Archive/170228

From BetaArchive Wiki

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:

  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

Additional query words:

Version : 4.0 5.0 5.0a 5.0c 7.0
Platform : MACINTOSH WINDOWS
Issue type : kbprb
Solution Type : kbworkaround


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: August 12, 1997
©1997 Microsoft Corporation. All rights reserved. Legal Notices.