Nested IF AND with Multiple logics

tamiz.vedachalam

Hello Experts,

I need to calculate SLA Breach, based on a ticket severity and Number of days consumed.

LOW - Greater than - 7 days

MEDIUM - Greater than - 4 days

HIGH - Greater than - 2 days

CRITICAL - Greater than - 1 day

Tried the following formula, but I am getting "#Invalid Data Type" error

=IF(AND(Severity@row = "Low", [Ticket Duration]@row > "7","Yes","No"),IF(AND(Severity@row = "Medium", [Ticket Duration]@row >"4","Yes","No"),IF(AND(Severity@row = "High", [Ticket Duration]@row >"2","Yes","No"),IF(AND(Severity@row = "Critical", [Ticket Duration]@row >"1","Yes","No")))))

Tried another formula - The output is blank

=IF(AND(Severity@row = "Low", [Ticket Duration]@row > "7"),IF(AND(Severity@row = "Medium", [Ticket Duration]@row >"4"),IF(AND(Severity@row = "High", [Ticket Duration]@row >"2"),IF(AND(Severity@row = "Critical", [Ticket Duration]@row >"1"),"Yes","No"))))

tamiz.vedachalam

@Genevieve P. - Would you be able to help me for the above query.

Razetto

@tamiz.vedachalam See if this version works out, I added the Yes after each: =IF(AND(Severity@row = "Low", [Ticket Duration]@row > "7"), "Yes", IF(AND(Severity@row = "Medium", [Ticket Duration]@row > "4"), "Yes", IF(AND(Severity@row = "High", [Ticket Duration]@row > "2"), "Yes", IF(AND(Severity@row = "Critical", [Ticket Duration]@row > "1"), "Yes", "No"))))

tamiz.vedachalam

Thank you Razetto,

partially worked. But when I am copying into Blank values, its throwing as No. Because I want to extend the formula to other rows too. Or is it wise option to copy only the row is filled.

Hollie205

If you are wanting it to remain blank when the rest of the row is blank you can add another if formula. That will make it leave the SLA blank if the severity is blank.

=IF(Severity@row="","",IF(AND(Severity@row = "Low", [Ticket Duration]@row > "7"), "Yes", IF(AND(Severity@row = "Medium", [Ticket Duration]@row > "4"), "Yes", IF(AND(Severity@row = "High", [Ticket Duration]@row > "2"), "Yes", IF(AND(Severity@row = "Critical", [Ticket Duration]@row > "1"), "Yes", "No")))))