MacXL: Errors Occur in Embedded IF Formulas (187584)
The information in this article applies to:
- Microsoft Excel 2001 for Mac
- Microsoft Excel 98 Macintosh Edition
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
REFERENCESFor more information about the IF statement, click the Office Assistant, type IF worksheet function, click Search, and then click a topic to view it.
NOTE: If the Assistant is hidden, click the Office Assistant button on the Standard toolbar.
| Modification Type: | Major | Last Reviewed: | 9/11/2002 |
|---|
| Keywords: | kbhowto KB187584 |
|---|
|