Large IF Statement with multiple logical test

C

ccamp88

New Member
Joined
Aug 26, 2021
Messages
4
Office Version
  1. 365
Platform
  1. Windows
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
 

Attachments

  • IF Statement question.png
    IF Statement question.png
    70.1 KB · Views: 10

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.
Sort by date Sort by votes
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:

Excel Formula: 
=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+","")))))))
 
Upvote 0
snjpverma said:
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:

Excel Formula: 
=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+","")))))))
Click to expand...
It worked! thanks I was close to figuring that out I just left off the , "" and had ))) issue. Thanks again
 
Upvote 0
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.

Excel Formula: 
=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.
 
Upvote 0
Alex Blakenburg said:
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.

Excel Formula: 
=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.
Click to expand...
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
 
Upvote 0
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.
 
Upvote 0
Alex Blakenburg said:
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.
Click to expand...
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",
""))),
""))
 
Upvote 0
You look to have one too many closing brackets after the 2nd IF(OR
 
Upvote 0
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
Excel Formula: 
=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",
""))),
"")))))
 
Upvote 0
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
ABCDEFGHIJKLMNOPQ
1
2
35-9
4
5Podiatry Volume8
6
7TypeTableTable1Table2Table3Table4Table5
8General Surgery VolumeTable10<30<30<20<20<5
9GYN VolumeTable133-51010-2022-422-355-10
10Ophthalmology VolumeTable266-102121-3055-944-71111+
11Pain General VolumeTable21111-143131-401010+88+
12GI VolumeTable21515+4141+
13Podiatry VolumeTable3
14Plastic Surgery VolumeTable3
15Neuro VolumeTable4
16Pain Implants VolumeTable4
17Dentistry VolumeTable4
18Ortho Total Joint VolumeTable5
Sheet13
Cell Formulas
RangeFormula
C3C3=VLOOKUP(L5,INDIRECT(VLOOKUP(C5,TypeTable,2,0)),2)
Named Ranges
NameRefers ToCells
TypeTable=Sheet13!$A$8:$B$18C3


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.
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Solved
Chart Help Please
Replies
1
Views
95
dmj120
D
C
  • Question Question
Create code that sends emails and creates a log
Replies
1
Views
265
cokeoverpepsi123
C
O
  • Solved
Populate headers based on column and merge amount for multiple words
Replies
7
Views
194
Omar M
O
K
  • Question Question
Help with difference between 2 dates including am/pm and check multiple entries to see if they occur at the same date and am/pm
Replies
1
Views
499
XLfriend777
XLfriend777
C
  • Question Question
Revenue Allocation Based on the Number of Weeks ,Days Months etc
Replies
2
Views
284
ChetanPuri
C

Forum statistics

Threads
1,223,236
Messages
6,170,912
Members
452,366
Latest member
TePunaBloke

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top