Microsoft KB Archive/187584

= MacXL: Errors Occur in Embedded IF Formulas =

Article ID: 187584

Article Last Modified on 1/23/2007

-

APPLIES TO


 * Microsoft Excel 2001 for Mac
 * Microsoft Excel 98 for Macintosh

-



This article was previously published under Q187584



SUMMARY
The versions of Microsoft Excel listed at the beginning of this article have a limit of seven levels of embedded, or "nested", IF statements. Eight or more IF statements embedded in the same formula causes Microsoft Excel to return the message "Error in Formula."



MORE INFORMATION
The following are examples of correct and incorrect formulas.

Example of Correct Formula
A total of eight IF statements are allowed, but only seven may be embedded. Notice that there is one IF statement that has seven embedded IF statements within it (for a total of 8). The following example will operate correctly. =IF(A1=1, 1, 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 Incorrect Formula
Notice there is one IF statement that has eight embedded IF statements within it (for a total of 9). =IF(A1=1, 1, 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

A Boolean approach can be used to overcome the limit of seven embedded IF functions. The following example could extend beyond the limits of seven embedded IF statements. =IF(A1=1, 1, 0) + IF(A1=2, 2, 0) + IF(A1=3, 3, 0) + IF(A1=4, 4, 0) + IF(A1=5, 5, 0) + IF(A1=6, 6, 0) + IF(A1=7, 7, 0) + IF(A1=8, 8, 0) + IF(A1=9, 9, 0) If the value of A1 is 5, then this function becomes = 0 + 0 + 0 + 0 + 5 + 0 + 0 + 0 + 0  = 5

