IF statement not working on formula "0"

New Contributor

=IFS(A2="Product","Y", A2="sku","",AND(AE2="0",A2="rule"),"N",AND(NOT(AE2="0"),A2="rule"),"Y")

Currently AE2 is a field with formulas that compute to 0, right now it is not recognizing this and calculating to yes for all values. 

4 Replies

@DirtyHippy 

 

If AE2 contains numeric zero, then the test should be AE2=0 without quotes around the zero.

 

And instead of NOT(AE2=0), simply write AE2<>0.

I've also tried the permutation =IFS(A2="Product","Y", A2="sku","",NOT(AND(AE2="0",A2="rule")),"Y",AND(AE2="0",A2="rule"),"N")

@DirtyHippywrote:

=IFS(A2="Product","Y", A2="sku","",AND(AE2="0",A2="rule"),"N",AND(NOT(AE2="0"),A2="rule"),"Y")

 

 

That formula returns #N/A if A2 can be anything other then "Product", "sku" or "rule".  For example, can A2 appear blank (empty or the null string)?

 

It is prudent to always have a TRUE part at the end.  For example:

 

=IFS(A2="Product", "Y", A2="sku", "", A2="rule", IF(AE2=0,"N", "Y"), TRUE, "")

 

Aside....  A2="sku","" is superfluous if it is the same as the TRUE result, like mine.

 

Alternatively, if A2 can be only "Product", "sku" or "rule":

 

=IFS(A2="Product", "Y", A2="sku", "", TRUE, IF(AE2=0,"N","Y"))

 

and =IFS(A32="Product","Y", A32="sku","",AND(AE32="0",A32="rule"),"N",NOT(AND(AE32="0",A32="rule")),"Y")