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]
 

Some videos you may like

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
7,985
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
7,985
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,089,420
Messages
5,408,122
Members
403,185
Latest member
sp646

This Week's Hot Topics

  • help please
    SORRY NOT ANY GOOD AT EXCEL SO HELP WOULD BE MUCH APPRECIATED this formula is in a sheet called ignore...
  • two formulas needed
    Hello, I'll try my best to explain this: First formula needed in Sheet1 cell A2: If Sheet1 cell B2 = Sheet2 cell B2 then return a 1. If not then...
  • Dynamic Counts
    Good afternoon, we are tidying up some data & the data seems to be growing quicker than we are tidying it up! What we confirm (by reviewing it...
  • Help Excel formula eliminate duplicate values and keep only 2 identical rows.
    as picture below column A has a duplicate value. but the values are not the same as the rule. sometimes 4 rows, sometimes 10 rows or 7 or 9...
  • Macro Compile Error Sub or Function not defined
    Hello, I am trying to run macros from a validation list, all macros have been created and run perfectly on there own but I'm getting a compile...
  • Last row combined with Current Region VBA
    I'm generally happy finding the last row of data through something like Lastrow = Cells(Rows.Count, "D").End(xlUp) but I don't always receive data...
Top