If AND or OR

Christine Menke

Hi,

I have use below formula to show all parts of my process steps. It works well with red and green balls. But I would like to add if one of below columns is yellow (pending) the result in this cell should be yellow as well.

How can I modify the fomula:

=IF([Dev sample needed?]@row = "", "", IF(OR([CAD upload on time ?]@row = "Green", [Pattern confirmed on time?]@row = "Green", [Tooling confirmed on time?]@row = "Green", [Materials confirmed on time?]@row = "Green"), "Green", "Red"))

Genevieve P.

Hi @Christine Menke

I believe your'e getting an error because of the spaces between the IF statements and their open parentheses.

IF( versus IF (

Try this:

=IF([Dev sample needed?]@row = "", "", IF(AND([CAD upload on time ?]@row = "Green", [Pattern confirmed on time?]@row = "Green", [Tooling confirmed on time?]@row = "Green", [Materials confirmed on time?]@row = "Green"), "Green", IF(OR([CAD upload on time ?]@row = "Yellow", [Pattern confirmed on time?]@row = "Yellow", [Tooling confirmed on time?]@row = "Yellow", [Materials confirmed on time?]@row = "Yellow"), "Yellow", "Red")))

Keep in mind that if you are looking into Status Symbol ball columns, you'll need to spell the text as "Yellow" with only one capital in order to display the ball, or recognize the ball colour. Otherwise it will look for the text, "YELLOW". Does that make sense?

Let me know if this works for you!

Cheers,

Genevieve

Rich Stowell

Christine,

First, your IF(OR statement means that if any of the column are green the you are reporting your process is Green - is this what you intend? If this is the case, it will be hard to get past this step in the logic as a "true" result will occur. Suggest you change this to "AND" so that ALL columns must be green before reporting the Process as "Green". If you do this, the try the following to report "Green", "Yellow" or "Red":

=IF (

[Dev sample needed?]@row = "",

"", 

IF (

AND (

[CAD upload on time ?]@row = "Green", 

[Pattern confirmed on time?]@row = "Green", 

[Tooling confirmed on time?]@row = "Green", 

[Materials confirmed on time?]@row = "Green"

), 

"Green", 

IF (

OR (

IF (

[CAD upload on time ?]@row = "Yellow",

"True",

"False"

), 

IF (

[Pattern confirmed on time?]@row = "Yellow", 

"True",

"False"

), 

IF (

[Tooling confirmed on time?]@row = "Yellow", 

"True",

"False"

), 

IF (

[Materials confirmed on time?]@row = "Yellow"

"True",

"False"

), 

), 

"Yellow", 

"Red"

)

)

)

Christine Menke

Thanks for your help Rich Stowell ,

but it's still not working. I get an unparseable error. Any idea to fix it?

=IF ([Dev sample needed?]@row= "","", IF (AND ([CAD upload on time ?]@row= "GREEN", [Pattern confirmed on time?]@row = "GREEN", [Tooling confirmed on time?]@row= "GREEN", [Materials confirmed on time?]@row= "GREEN"), "GREEN", IF (OR (IF ([CAD upload on time ?]@row = "YELLOW", "TRUE" , "FALSE"), IF ([Pattern confirmed on time?]@row= "YELLOW", "TRUE","FALSE"), IF ([Tooling confirmed on time?]@row = "YELLOW", "TRUE", "FALSE"), IF ([Materials confirmed on time?]@row= "YELLOW", "TRUE","FALSE"),), "YELLOW", "RED")))

Genevieve P.

Hi @Christine Menke

I believe your'e getting an error because of the spaces between the IF statements and their open parentheses.

IF( versus IF (

Try this:

=IF([Dev sample needed?]@row = "", "", IF(AND([CAD upload on time ?]@row = "Green", [Pattern confirmed on time?]@row = "Green", [Tooling confirmed on time?]@row = "Green", [Materials confirmed on time?]@row = "Green"), "Green", IF(OR([CAD upload on time ?]@row = "Yellow", [Pattern confirmed on time?]@row = "Yellow", [Tooling confirmed on time?]@row = "Yellow", [Materials confirmed on time?]@row = "Yellow"), "Yellow", "Red")))

Keep in mind that if you are looking into Status Symbol ball columns, you'll need to spell the text as "Yellow" with only one capital in order to display the ball, or recognize the ball colour. Otherwise it will look for the text, "YELLOW". Does that make sense?

Let me know if this works for you!

Cheers,

Genevieve

Christine Menke

Hi @Genevieve P

thanks a lot for explaining the small details. It works now :-)

Have a good day!

Genevieve P.

Hi Christine, I'm glad I could help!