Excel: Using Boolean Expression (Avoiding IF Statements)

Last reviewed: November 2, 1994
Article ID: Q63329

SUMMARY

To use Boolean expressions in Microsoft Excel, you must understand their underlying logic. The basis of Boolean analysis is that statements that evaluate as TRUE are evaluated as being equal to 1 (one), while statements that evaluate as FALSE are evaluated as being equal to 0 (zero).

One of the simplest examples of a Boolean expression is as follows:

   =A1=B1

This statement will evaluate as TRUE (1) if the value in cell A1 is equal to the value in cell B1, and as FALSE (0) otherwise.

MORE INFORMATION

The following IF() statement performs an operation based on whether or not A1=B1:

   =IF(A1=B1,A1*5.3,0)

This IF() statement compares the value in A1 to the value in B1, and if the two values are equal, multiplies the value in A1 by 5.3; if the values are not equal, the value returned is 0 (zero). The following Boolean formula is functionally equivalent to the previous IF() statement:

   =(A1=B1)*A1*5.3

This formula works in the following manner: if A1=B1, the first part of the formula (A1=B1) evaluates to TRUE, which Excel evaluates as the number 1 (one). If A1<>B1, the first part of the formula returns FALSE, which Excel evaluates as 0 (zero). This is diagrammed as follows:

   (TRUE)*A1*5.3   equals   1*A1*5.3   equals   A1*5.3,   or
   (FALSE)*A1*5.3  equals   0*A1*5.3   equals   0

Sometimes you need to check for more than one condition. Suppose we have the following problem:

If the value in cell A1 equals the value in B1, multiply A1 by 5.3. If the value of A1 equals the value in C1, multiply A1 by 10.6. Otherwise, return a 0 (zero). (We will assume B1<>C1.)

The following IF() statement returns the correct results:

   =IF(A1=B1,A1*5.3,IF(A1=C1,A1*10.6,0))

The following Boolean formula also returns the correct results:

   =((A1=B1)*A1*5.3)+((A1=C1)*A1*10.6)

This example contains the basic pattern for emulating a nested IF() statement, using a Boolean expression for each case. Each statement that evaluates as "TRUE" will return the correct number; all others will return a 0 (zero). The sum of the correct numbers and the zeros will equal the sum of the correct number.

Boolean expressions can also be used in place of AND() and OR() statements. For example:

   =IF(AND(A1=B1,B1=C1),A1*5.3,0)
   =((A1=B1)*(B1=C1))*A1*5.3

   =IF(OR(A1=B1,B1=C1),A1*5.3,0)
   =((A1=B1)+(B1=C1))*A1*5.3

Note: Boolean expressions evaluate more quickly than their standard counterparts. This may make a difference on a large spreadsheet with many nested calculations. Additionally, file space might be saved because a Boolean expression is often shorter than its standard counterpart.

KBCategory: kbother
KBSubcategory:
Additional reference words: noupd

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