nested if statements - multiple variables

[David Pelizzari]

I am attempting to write an if statement to check values in two columns, and
return a value based on the two.
column A is difference between two dates (today and date created)
column b is priority (low, medium, high and urgent are the options)
the values will be either compliant or non compliant based on the priority
and how long past the date in Column A. A low priority is 7 days, a medium
is 3 days, a high is 24 hours and an urgent is 8 hours. What I wrote is:

=IF($B:$B="Low",IF($A:$A<=7,"Compliant",IF($B:$B="Medium",IF($A:$A<=3,"Compliant",IF($B:$B="High",IF($A:$A<=1,"COMPLIANT","OUT
OF SLA"),"OUT OF SLA")),"OUT OF SLA")),"OUT OF SLA")

this works fine on the low priority, but it marks everything else as out of
compliance, not sure where I am getting this wrong...

 

[Luke M]

The way you have your formula structured, it's setup to look at entire array
and compare all, but you've given conflicting conditions so its going to
produce illogical results. (For instance, you first check to see if B = Low;
however, along the TRUE path, you check if B = Medium.

Assuming you are copying this formula down a column (an want a result for
each row), input this into C2:

=IF(OR(AND(B2="Low",A2<=7),AND(B2="Medium",A2<=3),AND(B2="High",A2<=1)),"COMPLIANT","OUT OF SLA")

 

[NBVC]

Firstly, that type of formula should be written for single cel
references only, if you are looking to compare ranges within on
formula, you need an array formula which is different....

Try something like:

=IF(OR(AND(B1="Low",A1<=7),AND(B1="Medium",A1<=3),AND(B1="High",A1<=1)),"COMPLIANT","OU
OF SLA")

copied down.. where A1 and B1 contain first set to compare

--
NBV

Where there is a will there are many ways.

'The Code Cage' (http://www.thecodecage.com