Large IF Statement with multiple logical test

[ccamp88]

Hello,

I am having a hard time with continuing to add onto this formula with multiple logical test. In my screenshot attached you will see in Cell M5 I have the following IF statement:
IF(OR(C5="General Surgery Volume",C5="GYN Volume"),IF(AND(L5>=0,L5<3),"<3",IF(AND(L5>=3,L5<6),"3 - 5",IF(AND(L5>=6,L5<11),"6 - 10",IF(AND(L5>=11,L5<15),"11 - 14",IF(L5>=15,"15+",""))))))

Which works perfectly if anything in Column C is General Surgery Volume or GYN volume. My question is how to I add onto this formula where as if something in column C is Ortho Volume or Cardiology volume it will return a different value? Below I have posted the IF statement that would apply to Ortho or Cardiology volume:
IF(OR(C5="Ortho Volume",C5="Cardiology Volume"),IF(AND(L5>=0,L5<5),"<5",IF(AND(L5>=5,L5<11),"5 - 10",IF(AND(L5>=11,L5<16),"11 - 15",IF(AND(L5>=16,L5<21),"16 - 20",IF(L5>=21,"21+",""))))))

my question is how do I combine these two statement together without it producing an error? If these statements are run separately I have no problem but whenever I go to combine them so that the value is dictated based on what column C is show I get a formula error. Please help I am having such a hard time finding a solution for this

 

[snjpverma]

Welcome to the Forum, @*ccamp88

IF(OR(C5="General Surgery Volume",C5="GYN Volume"),IF(AND(L5>=0,L5<3),"<3",IF(AND(L5>=3,L5<6),"3 - 5",IF(AND(L5>=6,L5<11),"6 - 10",IF(AND(L5>=11,L5<15),"11 - 14",IF(L5>=15,"15+","")))))put a comma and insert the 2nd formula here)

Like this:

=IF(OR(C5="General Surgery Volume",C5="GYN Volume"),IF(AND(L5>=0,L5<3),"<3",IF(AND(L5>=3,L5<6),"3 - 5",IF(AND(L5>=6,L5<11),"6 - 10",IF(AND(L5>=11,L5<15),"11 - 14",IF(L5>=15,"15+",""))))),IF(OR(C5="Ortho Volume",C5="Cardiology Volume"),IF(AND(L5>=0,L5<5),"<5",IF(AND(L5>=5,L5<11),"5 - 10",IF(AND(L5>=11,L5<16),"11 - 15",IF(AND(L5>=16,L5<21),"16 - 20",IF(L5>=21,"21+","")))))))

 

[ccamp88]

Welcome to the Forum, @*ccamp88

IF(OR(C5="General Surgery Volume",C5="GYN Volume"),IF(AND(L5>=0,L5<3),"<3",IF(AND(L5>=3,L5<6),"3 - 5",IF(AND(L5>=6,L5<11),"6 - 10",IF(AND(L5>=11,L5<15),"11 - 14",IF(L5>=15,"15+","")))))put a comma and insert the 2nd formula here)

Like this:

=IF(OR(C5="General Surgery Volume",C5="GYN Volume"),IF(AND(L5>=0,L5<3),"<3",IF(AND(L5>=3,L5<6),"3 - 5",IF(AND(L5>=6,L5<11),"6 - 10",IF(AND(L5>=11,L5<15),"11 - 14",IF(L5>=15,"15+",""))))),IF(OR(C5="Ortho Volume",C5="Cardiology Volume"),IF(AND(L5>=0,L5<5),"<5",IF(AND(L5>=5,L5<11),"5 - 10",IF(AND(L5>=11,L5<16),"11 - 15",IF(AND(L5>=16,L5<21),"16 - 20",IF(L5>=21,"21+","")))))))

It worked! thanks I was close to figuring that out I just left off the , "" and had ))) issue. Thanks again

 

[Alex Blakenburg]

For nested if statements that bracket numbers into groups, it easier if rely on the order of the if statements to put it into buckets so you don't need to use the "and" (between).
I also find it clearer if you use Alt+Enter to show the nesting.

=IF(OR(C5="General Surgery Volume",C5="GYN Volume"),
     IF(L5>=15,"15+",
     IF(L5>=11,"11 - 14",
     IF(L5>=6,"6 - 10",
     IF(L5>=3,"3 - 5",
     IF(L5>=0,"<3",
  ""))))),
IF(OR(C5="Ortho Volume",C5="Cardiology Volume"),
     IF(L5>=21,"21+",
     IF(L5>=16,"16 - 20",
     IF(L5>=11,"11 - 15",
     IF(L5>=5,"5 - 10",
     IF(L5>=0,"<5",
  ""))))),
""))

I personally would probably still tend to use VLookups and do it via the use of tables.

 

[ccamp88]

For nested if statements that bracket numbers into groups, it easier if rely on the order of the if statements to put it into buckets so you don't need to use the "and" (between).
I also find it clearer if you use Alt+Enter to show the nesting.

=IF(OR(C5="General Surgery Volume",C5="GYN Volume"),
     IF(L5>=15,"15+",
     IF(L5>=11,"11 - 14",
     IF(L5>=6,"6 - 10",
     IF(L5>=3,"3 - 5",
     IF(L5>=0,"<3",
  ""))))),
IF(OR(C5="Ortho Volume",C5="Cardiology Volume"),
     IF(L5>=21,"21+",
     IF(L5>=16,"16 - 20",
     IF(L5>=11,"11 - 15",
     IF(L5>=5,"5 - 10",
     IF(L5>=0,"<5",
  ""))))),
""))

I personally would probably still tend to use VLookups and do it via the use of tables.

I did not know that you could use Alt enter in order to break up the formula. That makes sense to be and makes it more clear as to clearly breaking out the two formulas

 

[Alex Blakenburg]

I am a bit surprised by how few people take advantage of that capability even though they wouldn’t dream of not indenting their code eg VBA to show the nesting. Glad you found it helpful.
The indenting is just done using the space bar. Neither impact the formula’s performance.

 

[ccamp88]

I am a bit surprised by how few people take advantage of that capability even though they wouldn’t dream of not indenting their code eg VBA to show the nesting. Glad you found it helpful.
The indenting is just done using the space bar. Neither impact the formula’s performance.

Ok so I am putting together the final touches on the formula. It is saying that the too many arguement and producing a #value. Where did I go wrong?

=IF(OR(C5="General Surgery Volume",C5="GYN Volume"),
IF(L5>=15,"15+",
IF(L5>=11,"11 - 14",
IF(L5>=6,"6 - 10",
IF(L5>=3,"3 - 5",
IF(L5>=0,"<3",
""))))),
IF(OR(C5="Ophthalmology Volume",C5="Pain General Volume",C5="GI Volume"),
IF(L5>=41,"41+",
IF(L5>=31,"31 - 40",
IF(L5>=21,"21 - 30",
IF(L5>=10,"10 - 20",
IF(L5>=0,"<3",
"")))))),
IF(OR(C5="Podiatry Volume",C5="Plastic Surgery Volume"),
IF(L5>=10,"10+",
IF(L5>=5,"5 - 9",
IF(L5>=2,"2 - 4",
IF(L5>=0,"<0",
""))))),
IF(OR(C5="Neuro Volume",C5="Pain Implants Volume",C5="Dentistry Volume"),
IF(L5>=8,"8+",
IF(L5>=4,"4 - 7",
IF(L5>=2,"2 - 3",
IF(L5>=0,"<2",
""))))),
IF(C5="Ortho Total Joint Volume"),
IF(L5>=11,"11+",
IF(L5>=5,"5 - 10",
IF(L5>=0,"<5",
""))),
""))

 

[Fluff]

You look to have one too many closing brackets after the 2nd IF(OR

 

[snjpverma]

You have entered extra Bracket after every "")))))

Also, this doesn't need an ending bracket IF(C5="Ortho Total Joint Volume")

It should be like this

=IF(OR(C5="General Surgery Volume",C5="GYN Volume"),
IF(L5>=15,"15+",
IF(L5>=11,"11 - 14",
IF(L5>=6,"6 - 10",
IF(L5>=3,"3 - 5",
IF(L5>=0,"<3",
""))))),
IF(OR(C5="Ophthalmology Volume",C5="Pain General Volume",C5="GI Volume"),
IF(L5>=41,"41+",
IF(L5>=31,"31 - 40",
IF(L5>=21,"21 - 30",
IF(L5>=10,"10 - 20",
IF(L5>=0,"<3",
""))))),
IF(OR(C5="Podiatry Volume",C5="Plastic Surgery Volume"),
IF(L5>=10,"10+",
IF(L5>=5,"5 - 9",
IF(L5>=2,"2 - 4",
IF(L5>=0,"<0",
"")))),
IF(OR(C5="Neuro Volume",C5="Pain Implants Volume",C5="Dentistry Volume"),
IF(L5>=8,"8+",
IF(L5>=4,"4 - 7",
IF(L5>=2,"2 - 3",
IF(L5>=0,"<2",
"")))),
IF(C5="Ortho Total Joint Volume",
IF(L5>=11,"11+",
IF(L5>=5,"5 - 10",
IF(L5>=0,"<5",
""))),
"")))))

 

[Eric W]

Also, after the third IF(OR, the "<0" should be "<2".

Just to show how much easier your formula would be if you stored your data on the sheet and used defined Names:

Book4
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1
2
3			5-9
4
5			Podiatry Volume									8
6
7	TypeTable			Table1			Table2			Table3			Table4			Table5
8	General Surgery Volume	Table1		0	<3		0	<3		0	<2		0	<2		0	<5
9	GYN Volume	Table1		3	3-5		10	10-20		2	2-4		2	2-3		5	5-10
10	Ophthalmology Volume	Table2		6	6-10		21	21-30		5	5-9		4	4-7		11	11+
11	Pain General Volume	Table2		11	11-14		31	31-40		10	10+		8	8+
12	GI Volume	Table2		15	15+		41	41+
13	Podiatry Volume	Table3
14	Plastic Surgery Volume	Table3
15	Neuro Volume	Table4
16	Pain Implants Volume	Table4
17	Dentistry Volume	Table4
18	Ortho Total Joint Volume	Table5
Sheet13

Cell Formulas
Range	Formula
C3	C3	=VLOOKUP(L5,INDIRECT(VLOOKUP(C5,TypeTable,2,0)),2)

Named Ranges
Name	Refers To	Cells
TypeTable	=Sheet13!$A$8:$B$18	C3

The tables you could put anywhere, even on another sheet.

Table1 is defined as $D$8:$E$12, Table2 is defined as $G$8:$H$12, etc.