XL: Err Msg: Error in Formula with More Than 7 Embedded IF's

ID: Q26348

The information in this article applies to:

Microsoft Excel for the Macintosh, versions 1.x, 2.x, 3.0, 4.0., 5.0
Microsoft Excel for Windows, versions 2.x, 3.0, 4.0, 5.0, 7.0
Microsoft Excel 97 for Windows

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 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 8 IF statements are allowed, but only 7 may be embedded. Notice that there is one IF statement with seven embedded within it (for a total of 8). The following example will operate correctly.


   =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

Notice there is one IF statement which has eight embedded within it (for a total of 9).


   =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


Keywords          : 
Version           : WINDOWS:2.x,3.0,4.0,5.0,7.0,97; MACINTOSH:1.x,2.x,3.0,4.0,5.0
Platform          : 
Issue type        :

Last Reviewed: March 11, 1999