Link to home
Start Free TrialLog in
Avatar of route217
route217Flag for United Kingdom of Great Britain and Northern Ireland

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"))))))))))))))))
Avatar of Rob Henson
Rob Henson
Flag of United Kingdom of Great Britain and Northern Ireland image

Can you upload a sample file, just the table with a 20-30 rows of data will suffice.

I suspect you can use a combination of OR and AND rather than lots of IFs.
Avatar of route217

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.
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.
ASKER CERTIFIED SOLUTION
Avatar of Rob Henson
Rob Henson
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Thanks Rob
Glad to help.