Microsoft KB Archive/214154

= XL2000: Error Message with Formula Containing More Than Seven Embedded IF Statements =

Article ID: 214154

Article Last Modified on 11/5/2003

-

APPLIES TO


 * Microsoft Excel 2000 Standard Edition

-



This article was previously published under Q214154





SUMMARY
Microsoft Excel has a limit of seven levels of embedded, or "nested," IF statements. Eight or more IF statements embedded in the same formula cause Microsoft Excel to return the following error message:

Error in Formula.



MORE INFORMATION
When more than seven levels of embedded IF statements are required, you need to create a function macro, which allows you to break your IF statement over several lines (or formulas).

Example of Correct Formula
A total of eight IF statements are allowed, but only seven may be embedded.

NOTE: The following example operates correctly; it consists of one IF statement, which has seven embedded within it (for a total of eight).   =IF(A1=1,0,IF(A1=2,2,IF(A1=3,3,IF(A1=4,4,IF(A1=5,5,IF

(A1=6,6,IF(A1=7,7,IF(A1=8,8,0))))))))

Example of an Incorrect Formula
NOTE: The following example consists of one IF statement, which has eight embedded within it (for a total of nine).   =IF(A1=1,0,IF(A1=2,2,IF(A1=3,3,IF(A1=4,4,IF(A1=5,5,IF

(A1=6,6,IF(A1=7,7,IF(A1=8,8,IF(A1=9,9,0))))))))) It is the last occurrence that causes the formula to fail.

Additional query words: nested if XL2000

Keywords: kbhowto KB214154

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.