If AND or OR formula

Christine Menke

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!

Paul Newcome

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.

Paul Newcome

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", .....................

Christine Menke

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.

Paul Newcome

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"))))

Christine Menke

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.

Paul Newcome

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.

Christine Menke

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"

Paul Newcome

Happy to help and glad you were able to get it figured out. 👍️