XL2000: Error Message with Formula Containing More Than Seven Embedded IF Statements (214154)


The information in this article applies to:

  • Microsoft Excel 2000
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.
Modification Type:MajorLast Reviewed:11/5/2003
Keywords:kbhowto KB214154
©2003 Microsoft Corporation. All rights reserved.