If Cell A value has Cell B value2 next to it, make Cell 3 based on the two

[programm]

Let's say the row with Fruit, State, Result is row 1, so the A,B,C and rows numbers are not cells. Result can be GOOD,BAD or IGNORE. State is True, False or None
So actual data starts from row 2 and columns are A,B,C. What formula needed in C2 that calculates

IF (all potatoes are TRUE OR NONE) AND (all tomatoes are TRUE OR NONE) then set Result to GOOD;
ELSE IF (all potatoes are TRUE OR NONE but a tomato is FALSE (one or all)) then set Result to BAD
ELSE (meaning if even one potato is FALSE) then set result to IGNORE

Obviously exclude the Pickle from any conditions but it is getting in the way, you cannot just use range of cells unless with exclusion.

	A	B	C
1	Fruit	State	Result
2	Potato	True	(formula needed)
3	Potato	True
4	Tomato	None
5	Pickle	False
6	Potato	True
7	Tomato	True

[pjwhitfield]

Could you just complete the table so that I can confirm the expected results?

[JohnTopley]

Try

Please Login or Register  to view this content.

[programm]

I will need to add another Pickle, so 8th row and new Result: Worst

	A	B	C
1	Fruit	State	Result
2	Potato	True	(formula needed)
3	Potato	True
4	Tomato	None
5	Pickle	False
6	Potato	True
7	Tomato	True
8	Pickle	True

The above formula works, thx, but I will need to advance it a little bit, could be done so?



Better show in diagram, here is also another

[JeteMc]

I don't understand why the result for row 8 should be worst as no Potato has a state of FALSE.
Here is the formula that I propose based on the diagram in post #4:

Please Login or Register  to view this content.

Based on the values in A2:B8, please tell us which results are incorrect and why.

[programm]

I don't understand why the result for row 8 should be worst as no Potato has a state of FALSE.

I should have made the table have one Potato = False then, for testing purposes, I will have such cases there where a Potato is false and it should go to 'Worst' as shown.

The system will be re-used and the true and false will differ, it is not the entire data and not all rows that would be. I am for the working formula logic-wise and syntax-wise.

===================

Thanks the above works, I also have a TBD which I did not post to not confuse, and I added without a problem as beginning of the IFs and it all works according to diagram. Posting this for reference and such:

Please Login or Register  to view this content.

[JeteMc]

You're Welcome. Thank You for the feedback and for marking the thread as 'Solved'. I hope that you have a blessed day.