Forum Discussion
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
- and =IFS(A32="Product","Y", A32="sku","",AND(AE32="0",A32="rule"),"N",NOT(AND(AE32="0",A32="rule")),"Y")
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"))
- 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")
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.
