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

 Last reviewed: November 30, 1994
Article ID: Q89790
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 "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

KBCategory: kbusage
KBSubcategory:

Additional reference words: 5.00 2.0 2.00 2.01 2.1 2.10 2.2 2.20 3.0 3.00
4.0 4.00 nest 4.0a 4.00a condition

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