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

MACINTOSH         | WINDOWS

The information in this article applies to:

• Microsoft Excel for the Macintosh, versions 1.x, 2.x, 3.0, 4.0
• Microsoft Excel for Windows, version 2.x, 3.0, 4.0

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)))))))))