nested if statements - multiple variables

D

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...
 
L

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")
 
N

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top