Need Help on SLA Breached that will look up cells

[ryanjuzz]

Hi Team,

Good day!

I need your help on my excel report. I wanted to lookup certain cells with conditions <,> or = referencing another cell before it states if SLA Breached TRUE or FALSE, 1 or 0.

See my table
A B C D
1 Tick No Sevrty Resolution TAT (mins) SLA Breached (T/F)

2 IN1123 High 112.11 FALSE
3 IN1291 Low 98.10 FALSE
4 IN1952 Med 130 FALSE
5 IN1267 High 123.01 TRUE
6 IN1615 Low 140.00 FALSE
7 IN1098 Critic 30 FALSE
8 IN1221 Low 231 TRUE

I wanted a formula starting D2-D8 that will simplify the condition below and condition & severity . And will show TRUE or FALSE like shown on D2-D8

CONDITIONS

CRITICAL SEVERITY;
If Resolution Time > 60(mins) SLA Breached = TRUE
If Res Tme <= 60(mins) SLA Breached = FALSE

HIGH SEVERITY;
If Resolution Time > 120(mins) SLA Breached = TRUE
If Res Tme <= 120(mins) SLA Breached = FALSE

MEDIUM SEVERITY;
If Resolution Time > 150(mins) SLA Breached = TRUE
If Res Tme <= 150(mins) SLA Breached = FALSE

LOW SEVERITY;
If Resolution Time > 180(mins) SLA Breached = TRUE
If Res Tme <= 180(mins) SLA Breached = FALSE

I hope someone can help me on the formula for D2

Thanks!!!

 

[dave3009]

Try

=C2>CHOOSE(MATCH(B2, {"Low","Med","High","Critic"},0), 180, 150, 120, 60)

Edit:

Better still

=C2>VLOOKUP(B2, {"Low",180;"Med",150;"High",120;"Critic",60}, 2, 0)

 

[ryanjuzz]

Hi Dave,

Thanks, will try this out. One more question instead of putting TRUE or FALSE, what formula can I use to put Met or Not Met instead?

BTW I am using Excel 2007 on winXP and 7...

THanks!!!

 

[dave3009]

Try this

=IF(C2>VLOOKUP(B2, {"Low",180;"Med",150;"High",120;"Critic",60}, 2, 0), "Met", "Not Met")