# 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 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. =INDIRECT(“Sheet1!B” & PrevRow)

Thanks for the tip and the great examples, Matthew.

Posted in Uncategorized

## 19 thoughts on “Nested Functions”

1. Nick Osdale-Popa says:

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. LoveYourBlog says:

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. Scott says:

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. Andrew says:

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. Doug Smith says:

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. brett says:

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. Suhail Mukhtar says:

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

8. Bill says:

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. Frank Kabel says:

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. Bill says:

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.

=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 says:

I forgot to mention that I had to modify Tung Liu’s formula a bit for my grading system.

Bill

12. Jon Peltier says:

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

13. Jon Peltier says:

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

14. Bill says:

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

15. Palencia says:

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

16. Matt says:

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

17. Cambralenta says:

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

18. Cambralenta says:

Sorry, “PROC” function is “Lookup”!… Anyone?!

19. Cambralenta says:

Thanks anyway, problem is solved!

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