XL98: Errors Occur in Embedded IF Formulas

ID: Q187584

The information in this article applies to:

Microsoft Excel 98 Macintosh Edition

SUMMARY

Microsoft Excel 98 Macintosh Edition has 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

REFERENCES

For more information about the IF statement, click Contents And Index on the Help menu (or on the Balloon Help menu if you are using a version of the Macintosh operating system earlier than 8.0), click the Index button in MS Excel Help, type the following text

IF worksheet function

and then click Show Topics. Select the "IF" topic, and click Go To. If you are unable to find the information you need, ask the Office Assistant.

Additional query words: XL98

Keywords : kbdta xlformula
Version : MACINTOSH:98
Platform : MACINTOSH
Issue type : kbhowto