Excel: Logicals in Array Formulas Return Unexpected Results

 Last reviewed: November 29, 1994
Article ID: Q25966
The information in this article applies to:
  • Microsoft Excel for Windows, versions 2.x, 3.0, 4.0
  • Microsoft Excel for the Macintosh, versions 1.x, 2.x, 3.0, 4.0
  • Microsoft Excel for OS/2, versions 2.2, 2.21, and 3.0

SUMMARY

In Microsoft Excel, when using a combination of a logical function within an array formula, you may obtain unexpected results.

Logical functions are evaluated in their entirety before the results are used in the formula.

Example

When using the following array formula to produce a histogram, the AND function is evaluated in its entirety before being considered as the logical argument to the IF function: 

   =SUM(IF(AND(range>num1,range<=num2),1,0))
For example, if the formula is as follows, where the cell reference A1:A3 contains the numbers 5, 12, and 30, you might expect the result of 1 (since only 12 is between 10 and 20): 

   =SUM(IF(AND(A1:A3>10,A1:A3<=20),1,0))
However, the result 0 (zero) is produced because the AND function is equivalent to the following: 

   AND(A1>10,A2>10,A3>10,A1<=20,A2<=20,A3<=20)
The above formula returns the value FALSE because not all the arguments return the logical value TRUE, which causes the entry with value 12 to not be counted. This behavior is by program design.

To work around this behavior, use a nested IF formula, such as the following: 

   =SUM(IF(range>num1,IF(range<=num2,1)))

KBCategory: kbusage
KBSubcategory:

Additional words: 1.0 1.00 1.03 1.04 1.06 1.5 1.50 2.1
2.10 2.10c 2.2 2.20 2.21 3.0 3.00 4.0 4.00 ISBLANK NOT ISERR ISERROR
ISLOGICAL ISNA ISNONTEXT ISNUMBER ISREF ISTEXT OR

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: November 29, 1994
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.