Get "PASS" or "FAILED" with multiple criteria-CodePudding

My formula displays "PASS" or "FAILED" based on multiple criteria. The problem is that it displays "PASS" when the cells G2:I2 are blank. The desired behavior is that it should display "FAILED" when one or more of the cells G2:I2 are blank. How do I make that happen?

=IF(and(G2<0.28, H2<0.28, I2<0.28, G2>-0.28, H2>-0.28, I2>-0.28,ISBLANK(M2)),"PASS","FAILED")

CodePudding user response：

Use ifs(), like this:

=ifs( 
  isblank(G2), "FAILED", 
  isblank(H2), "FAILED", 
  isblank(I2), "FAILED", 
  not(isblank(M2)), "FAILED", 
  not(isbetween(G2, -0.28, 0.28)), "FAILED", 
  not(isbetween(H2, -0.28, 0.28)), "FAILED", 
  not(isbetween(I2, -0.28, 0.28)), "FAILED", 
  true, "PASS" 
)

CodePudding user response：

You can use COUNTA() to check how many values are there inside a given RANGE, and use ArrayFormula() to apply calculation to every cell of a range, instead of checking the cell one by one.

=ArrayFormula(LAMBDA(RANGE,
 IF(
  AND(
   COUNTA(RANGE)=3,
   RANGE>-0.28,
   RANGE<0.28,
   ISBLANK($M$2)
  ),
  "PASS",
  "FAILED"
 )
)($G$2:$I$2))

CodePudding user response：

null (empty) value is considered as 0 so try:

=IF(AND(G2<>"",   H2<>"",   I2<>"", 
        G2<0.28,  H2<0.28,  I2<0.28, 
        G2>-0.28, H2>-0.28, I2>-0.28, ISBLANK(M2)), "PASS", "FAILED")