New Contributor

# IF statement not working on formula "0"

=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

# Re: IF statement not working on formula "0"

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.

# Re: IF statement not working on formula "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")

# Re: IF statement not working on formula "0"

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

# Re: IF statement not working on formula "0"

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