Complex Nested IF AND

damienlogos

New Member
Joined
Jan 16, 2016
Messages
6
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

Well-known Member
Joined
Dec 30, 2008
Messages
6,890
Office Version
2019
Platform
Windows
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.
 
Last edited:

damienlogos

New Member
Joined
Jan 16, 2016
Messages
6
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

Well-known Member
Joined
Dec 30, 2008
Messages
6,890
Office Version
2019
Platform
Windows
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

New Member
Joined
Jan 16, 2016
Messages
6
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]
 

Forum statistics

Threads
1,077,731
Messages
5,335,893
Members
399,056
Latest member
CityGirlLuv

Some videos you may like

This Week's Hot Topics

Top