route217
asked on
Nested if statement
Hi Experts
I have the following if statement which is not working...cannot see error..
Booking to Appt = IF(SLA[Work Type]="CLCM Call Booking", IF(SLA[Appt Made - Appt]<=SLA[Customer CLCM Call Booking SLA],"In Target",
IF(SLA[Work Type]="IMA HLM OHA Onsite", IF(SLA[Appt Made - Appt]<=SLA[Customer IMA HML OHA Onsite SLA],"In Target",
IF(SLA[Work Type]="IMA HML OHP Onsite", IF(SLA[Appt Made - Appt]<=SLA[Customer IMA HML OHP Onsite SLA],"In Target",
IF(SLA[Work Type]="IMA HML OHP Offsite", IF(SLA[Appt Made - Appt]<=SLA[Customer IMA HML OHP Offsite SLA],"In Target",
IF(SLA[Work Type]="IMA HML TIMA/VIMA", IF(SLA[Appt Made - Appt]<=SLA[Customer IMA HML TIMA/VIMA],"In Target",
IF(SLA[Work Type]="IMA Network OHP", IF(SLA[Appt Made - Appt]<=SLA[Customer Network OHP SLA],"In Target",
IF(SLA[Work Type]="IMA Network OHA", IF(SLA[Appt Made - Appt]<=SLA[Customer Network OHA SLA],"In Target",
IF(SLA[Work Type]="IMS", IF(SLA[Appt Made - Appt]<=SLA[Customer IMS SLA],"In Target","Out Of Target"))))))))))))))))
I have the following if statement which is not working...cannot see error..
Booking to Appt = IF(SLA[Work Type]="CLCM Call Booking", IF(SLA[Appt Made - Appt]<=SLA[Customer CLCM Call Booking SLA],"In Target",
IF(SLA[Work Type]="IMA HLM OHA Onsite", IF(SLA[Appt Made - Appt]<=SLA[Customer IMA HML OHA Onsite SLA],"In Target",
IF(SLA[Work Type]="IMA HML OHP Onsite", IF(SLA[Appt Made - Appt]<=SLA[Customer IMA HML OHP Onsite SLA],"In Target",
IF(SLA[Work Type]="IMA HML OHP Offsite", IF(SLA[Appt Made - Appt]<=SLA[Customer IMA HML OHP Offsite SLA],"In Target",
IF(SLA[Work Type]="IMA HML TIMA/VIMA", IF(SLA[Appt Made - Appt]<=SLA[Customer IMA HML TIMA/VIMA],"In Target",
IF(SLA[Work Type]="IMA Network OHP", IF(SLA[Appt Made - Appt]<=SLA[Customer Network OHP SLA],"In Target",
IF(SLA[Work Type]="IMA Network OHA", IF(SLA[Appt Made - Appt]<=SLA[Customer Network OHA SLA],"In Target",
IF(SLA[Work Type]="IMS", IF(SLA[Appt Made - Appt]<=SLA[Customer IMS SLA],"In Target","Out Of Target"))))))))))))))))
ASKER
Hi rob...tricky to upload a sample file...sorry.
OK, try this:
= IF(OR(
AND(SLA[Work Type]="CLCM Call Booking",[Appt Made - Appt]<=SLA[Customer CLCM Call Booking SLA]),
AND(SLA[Work Type]="IMA HLM OHA Onsite",SLA[Appt Made - Appt]<=SLA[Customer IMA HML OHA Onsite SLA]),
AND(SLA[Work Type]="IMA HML OHP Onsite",SLA[Appt Made - Appt]<=SLA[Customer IMA HML OHP Onsite SLA]),
AND(SLA[Work Type]="IMA HML OHP Offsite",SLA[Appt Made - Appt]<=SLA[Customer IMA HML OHP Offsite SLA]),
AND(SLA[Work Type]="IMA HML TIMA/VIMA",SLA[Appt Made - Appt]<=SLA[Customer IMA HML TIMA/VIMA]),
AND(SLA[Work Type]="IMA Network OHP",SLA[Appt Made - Appt]<=SLA[Customer Network OHP SLA]),
AND(SLA[Work Type]="IMA Network OHA",SLA[Appt Made - Appt]<=SLA[Customer Network OHA SLA]),
AND(SLA[Work Type]="IMS",SLA[Appt Made - Appt]<=SLA[Customer IMS SLA])),"In Target","Out Of Target")
Might need to check the placement of brackets, there should be a closing bracket after each pair within an AND statement.
= IF(OR(
AND(SLA[Work Type]="CLCM Call Booking",[Appt Made - Appt]<=SLA[Customer CLCM Call Booking SLA]),
AND(SLA[Work Type]="IMA HLM OHA Onsite",SLA[Appt Made - Appt]<=SLA[Customer IMA HML OHA Onsite SLA]),
AND(SLA[Work Type]="IMA HML OHP Onsite",SLA[Appt Made - Appt]<=SLA[Customer IMA HML OHP Onsite SLA]),
AND(SLA[Work Type]="IMA HML OHP Offsite",SLA[Appt Made - Appt]<=SLA[Customer IMA HML OHP Offsite SLA]),
AND(SLA[Work Type]="IMA HML TIMA/VIMA",SLA[Appt Made - Appt]<=SLA[Customer IMA HML TIMA/VIMA]),
AND(SLA[Work Type]="IMA Network OHP",SLA[Appt Made - Appt]<=SLA[Customer Network OHP SLA]),
AND(SLA[Work Type]="IMA Network OHA",SLA[Appt Made - Appt]<=SLA[Customer Network OHA SLA]),
AND(SLA[Work Type]="IMS",SLA[Appt Made - Appt]<=SLA[Customer IMS SLA])),"In Target","Out Of Target")
Might need to check the placement of brackets, there should be a closing bracket after each pair within an AND statement.
Don't worry about the file, I have created it just from the headers in the question.
The OR/AND combination gives "Out Of Target" as I have no Data. The Original nested IFs gives False, in other words its getting to an IF statement and getting False result with no option for the result.
The OR/AND combination gives "Out Of Target" as I have no Data. The Original nested IFs gives False, in other words its getting to an IF statement and getting False result with no option for the result.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks Rob
Glad to help.
I suspect you can use a combination of OR and AND rather than lots of IFs.