You can nest functions inside Excel formulas, but it’s limited to 7 levels of nesting. Nested formulas look something like this
=IF(A1=”One”,1,IF(A1=”Two”,2,IF(A1=”Three”,3,IF(A1=”Four”,4,IF(A1=”Five”,5,IF(A1=”Six”, 6,IF(A1=”Seven”,7,IF(A1=”Eight”,8,”N A”))))))))
If you wanted to test for Nine, you’d be out of luck. There are a few techniques for getting around that limt. Matthew sent me a couple that are worth sharing.
- Don’t use CONCATENATE – The ampersand (&) works the same as the function and doesn’t count against you.
- Put repeated part of your formulas in a Name
- Use VLOOKUP and put some data in a separate table
The above formula could be reduced with a table like this
For another example, take this formula
While not at the limit, it has two levels of nesting. If it was part of a larger formula, it could put the formula over. You can reduce the nested levels of this formula to zero by eliminating CONCATENATE and defining a name for the ROW()-1 part.
=INDIRECT(“Sheet1!B” & PrevRow)
Thanks for the tip and the great examples, Matthew.