MacXL: Errors Occur in Embedded IF Formulas

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