
Excel's 7 Nested Function Limit
See
Also Lookup Method
Here
It's quite common for users to encounter the 7 nested Functions in Excel.
For those not sure, a nested function is where we use the result of one
function as the argument for another.
If your functions are all IF Functions, then see
Lookup Method
. This by far the most
efficient and flexible way to get around the limit on IF functions.
Ok, so let's jump in with an example of where you may use this other method
which also gets around the limit.
Let's say you have a column of data (Column "A"). In this column there are a
total of 70 different numeric cells. That is, 1 to 70 in any order.
Based on the content of each cell, we need to perform a Tax calculation by
multiplying a number by a defined constant named "Tax" based on 8 different
criteria
CRITERIA
IF A1 = 1 to 7 then 7*Tax
IF A1 = 8 to 10 then 10*Tax
IF A1 = 11 to 20 then 20*Tax
IF A1 = 21 to 30 then 30*Tax
IF A1 = 31 to 40 then 40*Tax
IF A1 = 41 to 50 then 50*Tax
IF A1 = 51 to 60 then 60*Tax
IF A1 = 61 to 70 then 70*Tax
What need to do is make all the above criteria into 2
Named Formulas
Here is how, assuming we want the results in Column "B" relative to the row
in Column "A".
1) Select B1 and go to Insert>Name>Define.
2) Now in the "Names in Workbook" box enter:
Criteria1
3) In the "Refers to" box enter:
=IF(AND(A1>0,A1<8),7*Tax,IF(AND(A1>7,A1<11),10*Tax,IF(AND(A1>10,A1<21),20*Tax,IF(AND(A1>20,A1<31),30*Tax))))
4) Now click
Add.
2) Now in the "Names in Workbook" box enter: Criteria2
3) In the "Refers to" box enter:
=iF(AND(A1>30,A1<41),40*Tax,IF(AND(A1>40,A1<51),50*Tax,IF(AND(A1>50,A1<61),60*Tax,IF(AND(A1>60,A1<71),70*Tax))))
4) Now click
Add
Now we need to add the
defined constant
named "Tax"
1) Now in the "Names in Workbook" box enter:
Tax
2) In the "Refers to" box enter: =0.15
3) Finally click
Add and then OK.
Now add numbers 1 to 70 on the Worksheet Starting from A1. Use the
Fill Handle
(while holding down Ctrl) of A1 after entering number 1 into A1 and
reselecting it.
All that is left now is to make use of our 2 named formulas that total
8
different criteria. So, in B1 simply Enter:
=IF(Criteria1,Criteria1,Criteria2)
Then copy down.
Excel Dashboard Reports & Excel Dashboard Charts 50% Off Become an ExcelUser Affiliate & Earn Money
Special! Free Choice of Complete Excel Training Course OR Excel Addins Collection on all purchases totaling over $64.00. ALL purchases totaling over $150.00 gets you BOTH! Purchases MUST be made via this site. Send payment proof to [email protected] 31 days after purchase date.
Instant Download and Money Back Guarantee on Most Software
Excel Trader Package Technical Analysis in Excel With $139.00 of FREE software!
Microsoft ® and Microsoft Excel ® are registered trademarks of Microsoft Corporation. OzGrid is in no way associated with Microsoft
Some of our more popular products are below...
Convert Excel Spreadsheets To Webpages  Trading In Excel  Construction Estimators  Finance Templates & Addins Bundle  CodeVBA  SmartVBA  PrintVBA  Excel Data Manipulation & Analysis  Convert MS Office Applications To......  Analyzer Excel  Downloader Excel
 MSSQL Migration
Toolkit 
Monte Carlo Addin 
Excel
Costing Templates