Microsoft KB Archive/26348

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

Article ID: 26348

Article Last Modified on 11/16/2006

-

APPLIES TO


 * Microsoft Excel 97 Standard Edition
 * Microsoft Excel 95 Standard Edition

-



This article was previously published under Q26348





SUMMARY
Microsoft Excel has a limit of seven levels of embedded, or &quot;nested,&quot; IF statements. Eight or more IF statements embedded in the same formula cause Microsoft Excel to return the message &quot;Error in Formula.&quot;



MORE INFORMATION
When more than seven levels of embedded IF statements are required, you need to create a function macro, which allows you to &quot;break&quot; 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: KB26348

-

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

© Microsoft Corporation. All rights reserved.