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

• 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

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 "Error in formula" is displayed.



2. 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.

REFERENCES

"Online Help," version 5.0
"Function Reference," version 4.0, pages 225-228
"Function Reference," version 3.0, pages 125-127

Additional query words: 2.0 2.00 2.01 2.1 2.10 2.2 2.20 3.0 nest 4.00a condition

Keywords :
Version :
Platform :
Issue type :