Complex Nested IF AND

[damienlogos]

Hi everyone,

I could use some help.

I'm trying to draft a complex IF statement to break various customers into a green, yellow, orange, or red status. I have a column to specifically state this, which will use conditional formatting per color, so our leadership can more easily see which of our customers is in a state we should be concerned of. However, I've never drafted a complex IF statement like this which also uses AND statements. I've been working on it, but my sanity checks are showing it's not putting out the results I want. Could someone help me draft the statement? Basically, we're trying to see which of these customers with remaining usage has a transition plan documented and if they are going to be done with their transition by 12/1. If they have 0 usage or are closed, we can mark them as green, if they have a plan documented and they are not showing as being done after 12/1 they're yellow, if we haven't gotten a plan from them yet but have contacted them they're orange, and if their transition end date is after 12/1 or we haven't contacted them, they're red.

Statement as it stands (with errors):

[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]=IF([@[September Usage]]=0, "Green", IF([@[SBC Transition Status]]="Closed", "Green", IF(AND([@[September Usage]]>0, COUNTA([@[SBC Transition Plan]])=1, [@[Contact Attempts]]>0), "Yellow", IF(AND([@[SBC Transition Status]]="Open", [@[September Usage]]>0, [@[Contact Attempts]]>0, NOT([@[Customer Planned SBC Transition Date]]<"12/1/2019")), "Orange","Red"))))

Criteria:
Green: Has 0 usage in September or SBC Transition Status is "Closed"
Yellow: Has >0 usage in September, SBC Transition Status is "Open", "SBC Transition Plan" is not blank, and "Customer Planned SBC Transition Date" is not after 12/1/2019
Orange:

Has >0 usage in September, SBC Transition Status is "Open", "SBC Transition Plan" is blank, and "Customer Planned SBC Transition Date" is not after 12/1/2019, and contact attempts > 0
Red:

Has >0 usage in September, SBC Transition Status is "Open", and EITHER "Customer Planned SBC Transition Date" IS after 12/1/2019 or "Not Meeting" OR contact attempts = 0.​

_{^{<strike>
</strike>}}​

[/FONT]

 

[jasonb75]

See if this works

=IF(OR([@[September Usage]]=0, "Green",[@[SBC Transition Status]]="Closed"), "Green",IF(AND([@[September Usage]]>0, [@[SBC Transition Plan]]<>"", [@[Contact Attempts]]>0,[@[Customer Planned SBC Transition Date]]<DATEVALUE("12/1/2019")), "Yellow",IF(AND([@[September Usage]]>0,[@[SBC Transition Status]]="Open", [@[SBC Transition Plan]]="", [@[Contact Attempts]]>0, [@[Customer Planned SBC Transition Date]]< DATEVALUE("12/1/2019")), "Orange","Red")))

The formula could be made shorter by re-ordering some of the criteria but I've tried to keep it in line with your description to make it easier for you to follow.

You were missing a couple of criteria, but your main problems were incorrectly placed parentheses and using "12/1/2019" as a date. A date in double inverted commas is actually a text string so is not directly comparable to real dates, it needs to be coerced into numeric format first.

 

[damienlogos]

See if this works

=IF(OR([@[September Usage]]=0, "Green",[@[SBC Transition Status]]="Closed"), "Green",IF(AND([@[September Usage]=]=>0, [@[SBC Transition Plan]]<>"", [@[Contact Attempts]=]=>0,[@[Customer Planned SBC Transition Date]]<datevalue("12 1="" "yellow",if(and([@[september="" 2019")),="" usage]="]=">0,[@[SBC Transition Status]]="Open", [@[SBC Transition Plan]]="", [@[Contact Attempts]=]=>0, [@[Customer Planned SBC Transition Date]]< DATEVALUE("12/1/2019")), "Orange","Red")))

The formula could be made shorter by re-ordering some of the criteria but I've tried to keep it in line with your description to make it easier for you to follow.

You were missing a couple of criteria, but your main problems were incorrectly placed parentheses and using "12/1/2019" as a date. A date in double inverted commas is actually a text string so is not directly comparable to real dates, it needs to be coerced into numeric format first.

Thank you Jason! I should have realized about the DATEVALUE requirement. I'm still trying to craft the formula. It appears that in your version the portion "[

@[Customer Planned SBC Transition Date]]​

<datevalue("12 style="color: rgb(34, 34, 34); font-family: Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif; font-size: 13px; font-style: normal; font-variant: normal; font-weight: 400; letter-spacing: normal; orphans: 2; text-align: left; text-decoration: none; text-indent: 0px; text-transform: none; -webkit-text-stroke-width: 0px; white-space: normal; word-spacing: 0px;" 1="" "yellow",if(and([@[september="" 2019")),="" usage]="]=">0"</datevalue("12> is throwing an error, and there's nothing denoting the items that should be yellow. I'm trying to decipher exactly how your version reads, but sadly, being self taught I'm not quite grasping some of your syntax.

</datevalue("12>

 

[jasonb75]

The part that is throwing an error was something that I missed when I was typing the post, the board software interpreted part of the formula as html code and lost it into some kind of virtual oblivion.

Here's the correct formula, with a couple of criteria removed. To get past the 'green' status, the sept usage must be <> 0 and the status must be <> closed, so on the assumption that usage can not be negative and that status can only be open or closed, the subsequent checks for these 2 items are not needed.

I've also split the formula into 1 line for each colour to (hopefully) make it a bit easier to follow.

=IF(OR([@[September Usage]]=0,[@[SBC Transition Status]]="Closed"), "Green",
IF(AND([@[Contact Attempts]]>0,[@[Customer Planned SBC Transition Date]]< DATEVALUE("12/1/2019")), "Yellow",
IF(AND([@[SBC Transition Plan]]="", [@[Contact Attempts]]>0, [@[Customer Planned SBC Transition Date]]< DATEVALUE("12/1/2019")), "Orange",
"Red")))

 

[damienlogos]

The part that is throwing an error was something that I missed when I was typing the post, the board software interpreted part of the formula as html code and lost it into some kind of virtual oblivion.

Here's the correct formula, with a couple of criteria removed. To get past the 'green' status, the sept usage must be <> 0 and the status must be <> closed, so on the assumption that usage can not be negative and that status can only be open or closed, the subsequent checks for these 2 items are not needed.

I've also split the formula into 1 line for each colour to (hopefully) make it a bit easier to follow.

=IF(OR([@[September Usage]]=0,[@[SBC Transition Status]]="Closed"), "Green",
IF(AND([@[Contact Attempts]=]=>0,[@[Customer Planned SBC Transition Date]]< DATEVALUE("12/1/2019")), "Yellow",
IF(AND([@[SBC Transition Plan]]="", [@[Contact Attempts]=]=>0, [@[Customer Planned SBC Transition Date]]< DATEVALUE("12/1/2019")), "Orange",
"Red")))

Thank you so much! While I was waiting for your reply I tinkered and I got it, and checked the results, everything checks out. It's not as concise as yours, but here's mine. I'll study yours tomorrow and see if I can trim mine down while still making sure it works.

[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]=IF(OR([@[September Usage]]=0, ([@[SBC Transition Status]]="Closed")), "Green", IF(AND([@[September Usage]]>0, [@[SBC Transition Status]]="Open", [@[SBC Transition Plan]]<>"", [@[Contact Attempts]]>0, [@[Customer Planned SBC Transition Date]]<DATEVALUE("12/2/2019")), "Yellow", IF(AND([@[SBC Transition Status]]="Open", [@[September Usage]]>0, [@[SBC Transition Plan]]="", [@[Contact Attempts]]>0, ([@[Customer Planned SBC Transition Date]]<DATEVALUE("12/2/2019"))), "Orange",IF(AND([@[September Usage]]>0, [@[SBC Transition Status]]="Open", OR([@[Customer Planned SBC Transition Date]]>DATEVALUE("12/1/2019"), [@[Contact Attempts]]=0)), "Red", "ERROR"))))[/FONT]