Nested If/And but need and And/Or

ShariM

I am trying to have the Opportunity Status ball automatically turn green when the four columns to the right are Green OR Gray. I can get the formula to work with just "Green", but when I try to put the OR "Gray" into the formula, it results in a yellow ball or unparseable.

=IF(AND([Commercial Business Case Completed]1 = "Green", [Technical Case Provided]1 = "Green", [Legal Documents Provided]1 = "Green", [CSR's Provided]1 = "Green"), "Green", "Yellow")

Yellow ball results with following formula:

=IF(AND([Commercial Business Case Completed]@row = "Green", [Commercial Business Case Completed]@row = "Gray", [Technical Case Provided]@row = "Green", [Technical Case Provided]@row = "Gray", [Legal Documents Provided]@row = "Green", [Legal Documents Provided]@row = "Gray", [CSR's Provided]@row = "Green", [CSR's Provided]@row = "Gray"), "Green", "Yellow")

#Unparsable Result for formula below:

=IF(AND([Commercial Business Case Completed]9 = "Green"or='Gray", [Technical Case Provided]9 = "Green"or="Gray", [Legal Documents Provided]9 = "Green"or="gray", [CSR's Provided]9 = "Green"or="Gray"), "Green", "Yellow")

Thank you in advance for any help.

Genevieve P.

Hi @ShariM

In order to add in the "or Gray" instruction, you will want to do that by using the OR function for each column.

Try this:

=IF(AND(OR([Commercial Business Case Completed]@row = "Green", [Commercial Business Case Completed]@row = "Gray"), OR([Technical Case Provided]@row = "Green", [Technical Case Provided]@row = "Gray"), OR([Legal Documents Provided]@row = "Green", [Legal Documents Provided]@row = "Gray"), OR([CSR's Provided]@row = "Green", [CSR's Provided]@row = "Gray")), "Green", "Yellow")

Broken Down:

=IF(AND

(OR([Commercial Business Case Completed]@row = "Green", [Commercial Business Case Completed]@row = "Gray"),

OR([Technical Case Provided]@row = "Green", [Technical Case Provided]@row = "Gray"),

OR([Legal Documents Provided]@row = "Green", [Legal Documents Provided]@row = "Gray"),

OR([CSR's Provided]@row = "Green", [CSR's Provided]@row = "Gray")),

"Green", "Yellow")

Let me know if you would like further explanation of any of these rules. I used the following Help Center articles that you might want to take a look at: IF Function / AND Function / OR Function

Cheers!

Genevieve

Genevieve P.

Hi @ShariM

In order to add in the "or Gray" instruction, you will want to do that by using the OR function for each column.

Try this:

=IF(AND(OR([Commercial Business Case Completed]@row = "Green", [Commercial Business Case Completed]@row = "Gray"), OR([Technical Case Provided]@row = "Green", [Technical Case Provided]@row = "Gray"), OR([Legal Documents Provided]@row = "Green", [Legal Documents Provided]@row = "Gray"), OR([CSR's Provided]@row = "Green", [CSR's Provided]@row = "Gray")), "Green", "Yellow")

Broken Down:

=IF(AND

(OR([Commercial Business Case Completed]@row = "Green", [Commercial Business Case Completed]@row = "Gray"),

OR([Technical Case Provided]@row = "Green", [Technical Case Provided]@row = "Gray"),

OR([Legal Documents Provided]@row = "Green", [Legal Documents Provided]@row = "Gray"),

OR([CSR's Provided]@row = "Green", [CSR's Provided]@row = "Gray")),

"Green", "Yellow")

Let me know if you would like further explanation of any of these rules. I used the following Help Center articles that you might want to take a look at: IF Function / AND Function / OR Function

Cheers!

Genevieve

ron.judenberg112096

=IF(AND(OR([Commercial Business Case Completed]9 = "Green", [Commercial Business Case Completed]9 = "Gray"), OR([Technical Case Provided]9 = "Green", [Technical Case Provided]9 = "Gray"), OR([Legal Documents Provided]9 = "Green", [Legal Documents Provided]9 = "gray"), OR([CSR's Provided]9 = "Green", [CSR's Provided]9 = "Gray")), "Green", "Yellow")

ShariM

Perfect. It worked just as I intended. Thank you.

Genevieve P.

Happy to help! :)