Microsoft KB Archive/89790

{|
 * width="100%"|

Excel: 7th Nested IF in Formula Can't Contain AND and OR

 * }

-

The information in this article applies to:


 * Microsoft Excel for Windows, versions 2.x, 3.0, 4.0, 4.0a, 5.0
 * Microsoft Excel for the Macintosh, versions 2.x, 3.0, 4.0

-

SUMMARY
In Microsoft Excel, if a formula contains seven nested IF statements (the maximum allowable in Microsoft Excel), the seventh IF statement cannot contain both an AND and an OR statement. Attempting to enter an AND and an OR in the seventh IF statement results in the following message:

Error in formula

MORE INFORMATION
Microsoft Excel allows a maximum of seven nested IF statements within a single formula. However, the presence of both an AND and an OR within the seventh IF statement causes Microsoft Excel to behave as if the formula contains eight nested IF statements. The presence of an AND and OR is allowed in the first six IF statements.

Workaround
The only available workaround for this error is to not use both an AND and an OR statement in the seventh nested IF statement.

Examples of Incorrect and Correct Formulas

 * 1) Enter the following incorrect formula in cell A1 of a worksheet:

=IF(B1=1,1,IF(B1=2,2,IF(B1=3,3,IF(B1=4,4,IF(B1=5,5,IF

(B1=6,6,IF(AND(OR(B1=7,B2=7),B3=7),7,8))))))) and the error message &quot;Error in formula&quot; is displayed.
 * 1) Enter the following correct formulas which do not contain both an AND and an OR in the seventh IF statement:

=IF(B1=1,1,IF(B1=2,2,IF(B1=3,3,IF(B1=4,4,IF(B1=5,5,IF

(B1=6,6,IF(AND(B1=7,B2=7),7,8)))))))

=IF(B1=1,1,IF(B1=2,2,IF(B1=3,3,IF(B1=4,4,IF(B1=5,5,IF

(B1=6,6,IF(OR(B1=7,B2=7),7,8)))))))

and no error message is displayed.