<<Convert Excel Spreadsheets to Web Pages \| Trading Software That Operates Within Excel \| Convert Excel, Access & Other Databases \| Merge Excel Files>>

Seven Nested IF Formula Limitation

| | Information Helpful? Why Not Donate.

TRY OUT: Smart-VBA | Code-VBA | Analyzer-XL | Downloader-XL | Trader-XL| More Free Downloads.. Best Value: Finance Templates Bundle

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. ALLpurchases 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

Try out:Analyzer XL |Downloader XL |Smart VBA |Trader XL Pro (best value) |ConsoXL | MergeXL | O2OLAP for Excel | MORE>>

Excel Trader PackageTechnical 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