If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
nested if statements - multiple variables
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... |
#2
|
|||
|
|||
nested if statements - multiple variables
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") -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "David Pelizzari" wrote: 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... |
#3
|
|||
|
|||
nested if statements - multiple variables
Firstly, that type of formula should be written for single cell references only, if you are looking to compare ranges within one 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","OUT OF SLA") copied down.. where A1 and B1 contain first set to compare. -- NBVC Where there is a will there are many ways. 'The Code Cage' (http://www.thecodecage.com) ------------------------------------------------------------------------ NBVC's Profile: http://www.thecodecage.com/forumz/member.php?userid=74 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=125983 |
Thread Tools | |
Display Modes | |
|
|