Microsoft Excel Training- From Beginner to Expert in 6 Hours/ EXCEL DASHBOARD REPORTS

Seven Nested IF Formula Limitation

| | Information Helpful? Why Not Donate.

Get Around Excel's 7 Nested IF Functions/Formulas Limitation With Named Formulas

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 Add-ins 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

Microsoft Excel Training- From Beginner to Expert in 6 Hours/ EXCEL DASHBOARD REPORTS

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