Microsoft KB Archive/63329

{| = Excel: Using Boolean Expression (Avoiding IF Statements) =
 * width="100%"|

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 &quot;TRUE&quot; 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.
 * }