Nested Functions
A well known limitation to Excel is that you cannot "nest" more than 7 functions. For example, the following formula will fail because the limit is exceeded. =IF(Sheet1!$A$4=1,11,IF(Sheet1!$A$4=2,22,IF(Sheet1!$A$4=3,33, As a general "rule of thumb," if you have a formula with more than 7 nested statements, you should consider using a VBA function instead. However, if you do not want to use VBA, you can get around this limitation by creating a defined name that refers to part of the formula. Since defined formulas are evaluated separately, you can have one or more defined formulas which refer to large formulas, and combine these into a "master" formula. Suppose we wanted an nested IF formula to test: =IF(Sheet1!$A$4=1,11,IF(Sheet1!$A$4=2,22,IF(Sheet1!$A$4=3,33, Then create another named formula called SevenToThirteen, referring to the formula: =IF(Sheet1!$A$4=7,77,IF(Sheet1!$A$4=8,88,IF(Sheet1!$A$4=9,99, Finally, enter the "master" formula in the worksheet cell: |
|
|
|