Seven Nested IF Formula Limitation

NEW! More Books..

Add Excel Answers & Search To Your Google Toolbar Details

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

Current Special! Complete Excel Excel Training Course for Excel 97 - Excel 2003, only ~~$145.00~~. $59.95 Instant Buy/Download, 30 Day Money Back Guarantee & Free Excel Help for LIFE!

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(D8>0,D8<8),7*Tax,IF(AND(D8>7,D8<11),10*Tax,IF(AND(D8>10,D8<21),20*Tax,IF(AND(D8>20,D8<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

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 special@ozgrid.com 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