Nested Functions

You can nest functions inside Excel formulas, but it’s limited to 7 levels of nesting. Nested formulas look something like this

=IF(A1=”One”,1,IF(A1=”Two”,2,IF(A1=”Three”,3,IF(A1=”Four”,4,IF(A1=”Five”,5,IF(A1=”Six”, 6,IF(A1=”Seven”,7,IF(A1=”Eight”,8,”N A”))))))))

If you wanted to test for Nine, you’d be out of luck. There are a few techniques for getting around that limt. Matthew sent me a couple that are worth sharing.

  • Don’t use CONCATENATE – The ampersand (&) works the same as the function and doesn’t count against you.
  • Put repeated part of your formulas in a Name
  • Use VLOOKUP and put some data in a separate table

The above formula could be reduced with a table like this

NestedLimit

For another example, take this formula

=INDIRECT(CONCATENATE(“Sheet1!B”,ROW()-1))

While not at the limit, it has two levels of nesting. If it was part of a larger formula, it could put the formula over. You can reduce the nested levels of this formula to zero by eliminating CONCATENATE and defining a name for the ROW()-1 part.

NestedLimit2

=INDIRECT(“Sheet1!B” & PrevRow)

Thanks for the tip and the great examples, Matthew.

Posted in Uncategorized

19 thoughts on “Nested Functions

  1. Your example of nested IF() statements can be stated in an alternate way which then can be extended past the 7 nest limit.

    =IF(A1=”One”,1,0)+IF(A1=”Two”,2,0)+IF(A1=”Three”,3,0)+IF(A1=”Four”,4,0)+IF(A1=”Five”,5,0)+IF(A1=”Six”,6,0)+IF(A1=”Seven”,7,0)+IF(A1=”Eight”,8,0)+IF(A1=”Nine”,9,0)+IF(A1=”Ten”,10,0)+IF(A1=”Eleven”,11,0)+IF(A1=”Twelve”,12,0)+IF(A1=”Thirteen”,13,0)+IF(A1=”Fourteen”,14,0)+IF(A1=”Fifteen”,15,0)+IF(A1=”Sixteen”,16,0)+IF(A1=”Seventeen”,17,0)+IF(A1=”Eightteen”,18,0)+IF(A1=”Nineteen”,19,0)+IF(A1=”Twenty”,20,0)….

    This works best when using these types of comparisons.

    Granted setting up a VLOOKUP table is better, but you either need to set the table up in another worksheet or some obscure part of the current worksheet so that it doesn’t get edited/deleted accidently.

    Just my $.02 worth. :-)

  2. Darn. I came up with something almost identical to Nick, but it posted to the wrong blog entry. Although mine differs from Nick’s as it eliminates the IF statement altogether. My original comment:

    What about boolean gates? I was somewhat surprised they didn’t get a mention. A formula along the lines of:

    =(A1=”One”)*1+(A1=”Two”)*2+(A1=”Three”)*3+(A1=”Four”)*4+(A1=”Five”)*5+(A1=”Six”)*6+(A1=”Seven”)*7+(A1=”Eight”)*8+(A1=”Nine”)*9+(A1=”Ten”)*10

    …provides the ability to test beyond the nesting limit. I’m not suggesting for a moment that it’s pretty (I would normally use the method you suggested of a VLOOKUP table), but it works. And (IMHO) it’s a lot easier to follow than nested IFs as the logic is sequential instead of jumping all over the place, spaghetti code style.

  3. I’ve also found in my travels that OR can help you get around using IFs to their limit, such as:
    IF(OR(A1=”One’,A1=”Two”,A1=”Three”,A1=”Four”,etc),””,A1)

  4. Here’s a way I thought up (seems to work, fingers crossed)

    =IF(OR(A1={“Eight”;”Five”;”Four”;”Nine”;”One”;”Seven”;”Six”;”Ten”;”Three”;”Two”}),LOOKUP(A1,{“Eight”;”Five”;”Four”;”Nine”;”One”;”Seven”;”Six”;”Ten”;”Three”;”Two”},{8;5;4;9;1;7;6;10;3;2}),”No Criteria Found”)

    The criteria to be matched are in alphabetical order so the LOOKUP function works properly.

  5. Working off the cross-reference table, you could also use the following (entered as an array formula):

    {=SUMPRODUCT(IF(A1=D1:D10,1,0),E1:E10)}

    or as a standalone function (also an array formula):

    {=SUMPRODUCT(IF(A1={“Zero”;”One”;”Two”;”Three”;”Four”;”Five”;”Six”;”Seven”;”Eight”;”Nine”},1,0),{0;1;2;3;4;5;6;7;8;9})}

  6. Nesting IF’s is ugly. If you nest to 7 levels deep, by the time you come to placing the last few sets of brackets, my mind is spinning. Also, and more important, it is very difficult to edit later.

    Along the lines already mentioned … I use both AND and OR. Eg If(AND(A2>77, A2<101, B2<>”Blue”)=true,[whatever],[whatever else] ). Here, we test for 3 conditions, but you can have 30ish. Furthermore, the syntax is so much easier, you can troubleshoot or edit months later.

  7. Please mail me complex problems & their solutions for multiple nesting functions ( combination of if, or, and functions.
    thanks

  8. Aloha: I’m trying to set up a nested formula with 13 levels (for grading with “+” and “-“). I’ve tried a couple of your suggestions here as well as some of my own concoctions. I still can not get it to produce what I need. Basically, I want to be able to change a student’s numerical grade (e.g., 88.5) to a letter grade (e.g.,”B+”). Any suggestions? Mahalo. Bill

    =IF(F5>96.4,”A+”)+IF(F5>92.4,”A”)+IF(F5>89.4,”A-“)+IF(F5>86.4,”B+”)+IF(F5>82.4,”B”)+IF(F5>79.4,”B-“)+IF(F5>76.4,”C+”)+IF(F5>72.4,”C”)+IF(F5>69.4,”C-“)+IF(F5>66.4,”D+”)+IF(F5>62.4,”D”)+IF(F5>59.4,”D-“)+IF(F5<59.5,”F”)

  9. Hi
    for this kind of application use VLOOKUP and a lookup table. e.g. in column A and B enter:
    A B
    1 0 F
    2 59.5 D-
    3 62.5 D
    4 66.5 D+
    ….

    Now use a formula such as
    =VLOOKUP(F5,$A$1:$B$20,2,TRUE)

    Frank

  10. Thanks Frank. While I was waiting, I found another website at Ball State Univ. Tung Liu provided this solution. Rather cumbersome but it worked. I’ll have to try your suggestion as well.

    http://web.bsu.edu/tliu/faq/

    =IF(E5>=72.5,
    IF(E5<76.5,” C”,IF(E5<79.5,” C+”,
    IF(E5<82.5,” B -“,IF(E5<86.5,” B”,IF(E5<89.5,” B+”,
    IF(E5<92.5,” A -“,IF(E5<96.5,”A”,”A+”))))))),
    IF(E5>=69.5,” C -“,IF(E5>=66.5,” D+”,
    IF(E5>=62.5,” D”,IF(E5>=59.5,” D -“,” F”)))))

    Bill

  11. Bill –

    What happens if you change the cutoff values for the grades? Dozens of formulas will have to be changed. Sure, you can just copy-paste, but you may miss one, and someone misses an A because of a clerical error.

    The nice thing about a lookup table like Frank’s is that you can see at a glance what the cutoffs are, and you only have to make a change in one place instead of everywhere the logic is invoked.

    – Jon

  12. The way to nest Dick’s first formula more efficiently is to break each if into roughly equal pieces. His first IF,

    IF(A1=”One”

    breaks the problem into “One” and {Everything Else}. In the complex formula below, I’ve broken the problem into {“Zero”,”One”,”Two”,”Three”,”Four”} and everything else. Then I break this into {“Zero”,”One”} and the rest of {“Zero”,”One”,”Two”,”Three”,”Four”}, which is {“Two”,”Three”,”Four”}. And so on. Here’s the formula:

    =IF(OR(A1={“Zero”,”One”,”Two”,”Three”,”Four”}),IF(OR(A1={“Zero”,”One”}),IF(A1=”Zero”,0,1),IF(OR(A1={“Two”,”Three”}),IF(A1=”Two”,2,3),4)),IF(OR(A1={“Five”,”Six”,”Seven”,”Eight”,”Nine”}),IF(OR(A1={“Five”,”Six”}),IF(A1=”Five”,5,6),IF(OR(A1={“Seven”,”Eight”}),IF(A1=”Seven”,7,8),9)),”No Match”))

    I don’t know how many levels I’ve nested, but I think I could go further without too much trouble. It’s still not as quick or easy as building a lookup table, but it shows a way to nest the formula more efficiently.

    Here’s what I get in the worksheet:

    Zero0
    One1
    Two2
    Three3
    Four4
    Five5
    Six6
    Seven7
    Eight8
    Nine9
    ElseNo Match

    – Jon

  13. Jon, thanks for the comment. I’ve elected to go with Frank’s suggestion of using the VLOOKUP command. I had to tweak it slighty from .5s to .4s But it’s working just fine.

    Bill

  14. Get Past the 7 Nested IF limit

    I need to implement a more than 7 IF nested Statements in a Master Excel sheet I am creating. It is a Tolerance Table that I need to put several times across the sheet and across the book. I know it can be done in VBA but I am not an expert in VBA.

    Of course I tried to write the whole formula in EXCEL although I know it was not going to be accepted.

    Average % intervalsTolerance
    99and22
    97-98and4-Mar3
    94-96and7-May4
    91-93and10-Aug5
    87-90and14-Nov6
    82-86and15-197
    76-81and20-258
    70-75and26-319
    60-69and32-4110
    51-59and42-5011

    =IF(CELL=2,”2?,IF(CELL

  15. I was wondering if there is any way to increase the amount of conditional formats in a worksheet beyond 3? Thanks

  16. I’m working around this formula (it’s in Portuguese: SE=IF;E=AND; PROC=FIND):

    =SE(É.ERRO(SE(E(G108=”INF A”;B108=”M”);PROC(F108;{0;1954;2100;2395;2544;4000};{1;2;3;4;5});SE(E(G108=”INF A”;B108=”F”);PROC(F108;{0;1954;2100;2395;2544;4000};{1;2;3;4;5});SE(E(G108=”INF B”;B108=”M”);PROC(F108;{0;1954;2100;2395;2544;4000};{1;2;3;4;5});SE(E(G108=”INF B”;B108=”F”);PROC(F108;{0;1728;1844;2075;2191;4000};{1;2;3;4;5});SE(E(G108=”INIC”;B108=”M”);PROC(F108;{0;2016;2207;2589;2780;4000};{1;2;3;4;5});SE(E(G108=”INIC”;B108=”F”);PROC(F108;{0;1759;1874;2102;2217;4000};{0;1;2;3;4;5});SE(E(G108=”JUV”;B108=”M”);PROC(F108;{0;2271;2416;2706;2851;4000};{1;2;3;4;5});SE(E(G108=”JUV”;B108=”F”);PROC(F108;{0;1743;1854;2078;2189;4000};{1;2;3;4;5});SE(E(G108=”JUN”;B108=”M”);PROC(F108;{0;2084;2320;2795;3034;4000};{1;2;3;4;5});SE(E(G108=”JUN”;B108=”F”);PROC(F108;{0;1593;1689;1881;1977;4000};{1;2;3;4;5}))))))))))));”-“;SE(E(G108=”INF A”;B108=”M”);PROC(F108;{0;1954;2100;2395;2544;4000};{1;2;3;4;5});SE(E(G108=”INF A”;B108=”F”);PROC(F108;{0;1954;2100;2395;2544;4000};{1;2;3;4;5});SE(E(G108=”INF B”;B108=”M”);PROC(F108;{0;1954;2100;2395;2544;4000};{1;2;3;4;5});SE(E(G108=”INF B”;B108=”F”);PROC(F108;{0;1728;1844;2075;2191;4000};{1;2;3;4;5});SE(E(G108=”INIC”;B108=”M”);PROC(F108;{0;2016;2207;2589;2780;4000};{1;2;3;4;5});SE(E(G108=”INIC”;B108=”F”);PROC(F108;{0;1759;1874;2102;2217;4000};{0;1;2;3;4;5});SE(E(G108=”JUV”;B108=”M”);PROC(F108;{0;2271;2416;2706;2851;4000};{1;2;3;4;5});SE(E(G108=”JUV”;B108=”F”);PROC(F108;{0;1743;1854;2078;2189;4000};{1;2;3;4;5});SE(E(G108=”JUN”;B108=”M”);PROC(F108;{0;2084;2320;2795;3034;4000};{1;2;3;4;5});SE(E(G108=”JUN”;B108=”F”);PROC(F108;{0;1593;1689;1881;1977;4000};{1;2;3;4;5}))))))))))))

    It works fine on excel2007 but i can’t use in excel2003. Can someone help!? Thanks


Posting code? Use <pre> tags for VBA and <code> tags for inline.

Leave a Reply

Your email address will not be published.