Pass/Fail condition from few columns

adam

Hi,

 

I would like to do the following as shown in the PF_COND column with the limit set to 20. From these COND1 to COND3 columns if any of the row more than 20, then it's fail. For example, at row2, the SN is failing at COND2 and COND3.

My problem is with the formula when more than 1 condition fail, it did not recognized the AND operator and instead reported a single failed condition. Please suggest what shall be the right solution.

 

SN	RAD	COND1	COND2	COND3	PF_COND
M101	1	18	16	17	PASS
M101	2	16	21	23	FAIL COND2 and COND3
M120	1	24	22	16	FAIL COND1 and COND2
M120	2	19	18	18	PASS
M112	1	17	16	16	PASS
M112	2	26	16	18	FAIL COND1

 

Formula used:

If( :COND1 > 20, "FAIL COND1",
If( :COND2 > 20, "FAIL COND2",
If( :COND3 > 20, "FAIL COND3",
If( :COND1 > 20 & :COND2 > 20, "FAIL COND1 AND COND2",
If( :COND1 > 20 & :COND3 > 20, "FAIL COND1 AND COND3",
If( :COND2 > 20 & :COND3 > 20, "FAIL COND2 AND COND3",
If( :COND1 > 20 & :COND2 > 20 & :COND3 > 20, "FAIL COND1, COND2 AND COND3",
"PASS"
)))))))

 

Thank you.

Kevin_Anderson

Hi, adam!

 

George Boole looks displeased.

 

But then again, George Boole may have always looked displeased...

 

If any of the variables COND1, COND2, or COND3 were above 20, the logic was resolved and further conditions were not evaluated.  Of course the AND operator would never be evaluated.

 

Think like George Boole.  I suggest you try the following:

 

If(
:COND1 > 20 & :COND2 > 20 & :COND3 > 20, "FAIL COND1 and COND2 and COND3",
:COND1 > 20 & :COND2 > 20, "FAIL COND1 and COND2",
:COND1 > 20 & :COND3 > 20, "FAIL COND1 and COND3",
:COND2 > 20 & :COND3 > 20, "FAIL COND2 and COND3",
:COND1 > 20, "FAIL COND1",
:COND2 > 20, "FAIL COND2",
:COND3 > 20, "FAIL COND3",
"PASS"
);

 

Write conditional statements from the least probable to the most probable for performance, but you must get the logic correct or the additional performance means you get junk effectively and efficiently.

 

Good luck.