Microsoft KB Archive/25966

-

{| 1.x 2.x 3.00 4.00 | 2.x 3.00 4.00 | 2.2 2.21 3.00 MACINTOSH        | WINDOWS       | OS/2 kbusage The information in this article applies to:
 * width="100%"|


 * 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)))
 * }

-

Last reviewed: December 1, 1994

© 1998 Microsoft Corporation. All rights reserved. Terms of Use.