If AND or OR
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"))
Best Answer
-
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
Need more information? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao!👋 | Global Discussions
Answers
-
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"
)
)
)
-
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")))
-
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
Need more information? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao!👋 | Global Discussions
-
-
Hi Christine, I'm glad I could help!
Need more information? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao!👋 | Global Discussions
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 66.1K Get Help
- 430 Global Discussions
- 149 Industry Talk
- 490 Announcements
- 5.2K Ideas & Feature Requests
- 85 Brandfolder
- 154 Just for fun
- 74 Community Job Board
- 499 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 305 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!