I would like to test a cell against two conditions at 3 different times. This is for working tickets that have a number of minutes assigned for 3 different priorities.

Priority    Completed in (min)  SLA Test
1           15                  Pass
2           20                  Pass
3           70                  Fail

Priority 1 = 15, 2 = 30, 3 = 60

If the priority in C1 is equal to 1,2 or 3 and matches the number of minutes on the top right-hand side, return "pass" or "fail". I'm not sure how to run the tests "if" independently from one another

=IF(AND(C7=1,I7<=15) , "Pass", "Fail", IF(AND(c7=2,i7<=60) , "Pass", "Fail")

Pass or Fail after testing the 3 different priorities

1 Answers

0
EEM On

Assuming the table is located at C6:D9

Try any of these formulas:

  1. Nested IFs

    = IF( C7 = 1, IF( D7 <= 15, "Pass", "Fail"), IF( C7 = 2, IF( D7 <= 30, "Pass", "Fail"), IF( C7 = 3, IF( D7 <= 60, "Pass", "Fail") ) ) )

  2. Combining CHOOSE & IF:

    = CHOOSE( C7, IF( D7 <= 15, "Pass", "Fail"), IF( D7 <= 30, "Pass", "Fail"), IF( D7 <= 60, "Pass", "Fail") )

enter image description here