Nested If Functions in Excel 2007

By Ken Burnside

Excel's If function is used as a basic Boolean operator, and it gets used for a number of simple data evaluation functions. The basic format of the If statement has a mathematical function, which if it evaluates to false, returns the first value, and if it evaluates to true, returns the second value. By making If statements that nest, it's possible to build extensive conditional logic structures.

Limits on Nesting

Prior to Excel 2007, Excel formulas were limited to seven levels of nesting. In Excel 2007 and 2010, the maximum number of levels of nesting that a formula can have has been raised to 64. A related issue is that in Excel 2003 and earlier, formulas were limited to 255 characters; they are now limited to 1,023 characters in Excel 2007. When opening an Excel 2007 worksheet in Excel 2003 and earlier, formulas that cannot evaluate under the older version will show an #ERR result.

Performance Optimization

Nested If statements should be structured carefully; Excel will evaluate each step along the If statement as a separate calculation. Try to structure your formula such that the most common "Evaluate to True" statements come earlier in the formula rather than later to prevent Excel from running through more steps than it has to.

Combination With And and Or Functions

Excel's And function can remove most of the need to make complicated and difficult-to-maintain nested If statements. For example, "=IF(AND(A1=2,A2=4,A3=7),FALSE, TRUE)" gets the same results as "IF(A1=2,IF(A2=4,IF(A3=7, FALSE,TRUE)))" but is easier to modify and add additional logical checks to. It also doesn't run into the nesting limit, though an And function is limited to 255 arguments in Word 2007. The Or function works similarly, but instead of evaluating if all condition are True, evaluates as True if any one of the conditions specified is true.

Vlookup as an Alternative

Nested If statements are commonly used to evaluate the values of cells and return a response. The archetypal example of using a nested If statement Is to convert percentage grades on a teacher's grade book into letter grades. Because of how Excel processes data, this can be handled more efficiently by defining a table and using the Vlookup function to cross index the value of a cell with a column of values and return the result. This is easier to maintain and computationally less resource intensive than using nested If statements.