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


The information in this article applies to:

  • Microsoft Excel 97 for Windows
  • Microsoft Excel for Windows 95
This article was previously published under Q26348

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.
Modification Type:MinorLast Reviewed:8/15/2005
Keywords:KB26348
©2004 Microsoft Corporation. All rights reserved.