Complex Nested IF AND

damienlogos

New Member
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
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
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
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
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]
 

Some videos you may like

This Week's Hot Topics

  • Importing multiple excel files into one spreadsheet
    Hi, I'm trying to import multiple excel files (with the same format into a single spreadsheet) so that each day's file is listed underneath the...
  • find many based on a certain criteria
    good evening, I hope someone can help me? I have a workbook sheet 2 contains lots of data.... I would like to be able to find anything on sheet...
  • How to copy multiple rows using If
    Hi all, I'm very new to VBA and have written this simple code to copy certain cells if a certain cell within that row contains any data. I need...
  • VBA If statement
    Dear All, I have two dates, where I'd like a message box to pop, if the dates are between this criteria. [CODE] sDate1 = #10/1/2019#...
  • Text Format
    I have a sheet for user to keyin the data. The format of the data can be 451 / 1903, 0012 / 9908 or 00287 / 0099. The number after the "/" is...
  • Syntax errors
    Good Morning, Trying to compile a workbook, I keep getting a few errors. Here are the first two: [code=rich]Syntax Error: Function...
Top