If AND or OR formula
Hi Experts,
I would like to extend my formula in column Tech Pack confirmed on time. It works well with the color balls.
=IF([Dev sample needed?]@row = "", "not needed", 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")))
But now I need to add, if "not needed" is in the other columns it should be counted as "Green" or should not be counted.
E.g. the result in the 2nd and 3rd row should be "Green" and not red.
Thanks in advanced!
Best Answer

It depends on what exactly your priorities are. The formula will read from left to right and stop at the first true value. If you want yellows to take priority over not needed, then the yellow IF should come before the not needed IF.
My suggestion would be to write out how you want the formula to function keeping in mind priorities and how nested IFs work (if it makes it to the 3rd IF then the first two must be false).
If this column is blank > Not Needed
If all columns are Green > Green
If any are Red > Red
If any are Yellow > Yellow
If the first column is not blank and there are no reds, yellows, or greens (meaning all is false) > Not Needed
Put your IF statements in this order as well
IF([Column Name]@row = "", "Not Needed", <<>>)
IF(AND(all_columns_green), "Green", <<>>)
IF(OR(any_columns_red), "Red", <<>>)
IF(OR(any_columns_yellow), "Yellow", <<>>)
"Not Needed"
Of course you may need to adjust the orders to fit your preferred priority, but this exercise will help you get everything laid out the way you need it to be.
Now work from the bottom up. Put the "Not Needed" in place of the <<>> in the 4th IF. Take what you now have and put it in place of the <<>> in the 3rd IF. Take that new string and drop it in place of the <<>> in the 2nd IF. Take all of this now and drop it in place of the <<>> in the 1st IF.
Throw an = on the front of this final string and you should have your working formula.
Answers

You would need another IF/OR added in the same way as the rest of your IF/ORs.
..............................IF(OR([Column1]@row = "not needed", [Column2]@row = "not needed", ......................), "Green", .....................

Hi @Paul Newcome,
I have adjusted it to this:
=IF([Dev sample needed?]@row = "", "not needed", 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([Tooling confirmed on time?]@row = "not needed", [Pattern confirmed on time?]@row = "not needed", [Materials confirmed on time?]@row = "not needed"), "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"))))
But now I have the problem, that it always shows green, if it "green" or "not needed". But it should switch to yellow if one of the dots is yellow and the same for red.
I played around with the the( yellow) part but as this is already a OR function I'm not able get it workable.
Would be great if you could help.

Double check the portion in bold. You are basically saying that if any are "Not Needed" then "Green".
=IF([Dev sample needed?]@row = "", "not needed", 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([Tooling confirmed on time?]@row = "not needed", [Pattern confirmed on time?]@row = "not needed", [Materials confirmed on time?]@row = "not needed"), "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"))))

Thanks @Paul Newcome you are right. But I have no idea how to make it right?💡
I was thinking to not count if "not needed" is mentioned in the cell, but not sure how to make a formula out of it.
Or if e.g. ....If column 2 @ row is Green or "not needed" than Green
......If column 2 @row is Yellow or "not needed" than Yellow
......If column 2 @row is Red or "not needed" than Red
but I'm still not an expert with this if/ or / and formulas and not sure how I combine it, that it will work.
Would be great if somebody could help me.

It depends on what exactly your priorities are. The formula will read from left to right and stop at the first true value. If you want yellows to take priority over not needed, then the yellow IF should come before the not needed IF.
My suggestion would be to write out how you want the formula to function keeping in mind priorities and how nested IFs work (if it makes it to the 3rd IF then the first two must be false).
If this column is blank > Not Needed
If all columns are Green > Green
If any are Red > Red
If any are Yellow > Yellow
If the first column is not blank and there are no reds, yellows, or greens (meaning all is false) > Not Needed
Put your IF statements in this order as well
IF([Column Name]@row = "", "Not Needed", <<>>)
IF(AND(all_columns_green), "Green", <<>>)
IF(OR(any_columns_red), "Red", <<>>)
IF(OR(any_columns_yellow), "Yellow", <<>>)
"Not Needed"
Of course you may need to adjust the orders to fit your preferred priority, but this exercise will help you get everything laid out the way you need it to be.
Now work from the bottom up. Put the "Not Needed" in place of the <<>> in the 4th IF. Take what you now have and put it in place of the <<>> in the 3rd IF. Take that new string and drop it in place of the <<>> in the 2nd IF. Take all of this now and drop it in place of the <<>> in the 1st IF.
Throw an = on the front of this final string and you should have your working formula.

Happy New Year @Paul Newcome ;
not easy to solve it, but with your support I found the right formula thanks a lot.
It works like this:
IF([Column Name]@row = "", "Not Needed", <<>>)
IF(AND(all_columns_green), "Green", <<>>)
IF(OR(any_columns_red), "Red", <<>>)
IF(OR(any_columns_yellow), "Yellow", <<>>)
"Green"

Happy to help and glad you were able to get it figured out. 👍️
Help Article Resources
Categories
Check out the Formula Handbook template!