Excel not returning the right value in using "IF" function-CodePudding

I am trying to see any of 3 values are not "#VALUE!" by applying this statement:

These values are in columns B,C and D. it works in most rows but not all.

=IF(B1>0,1,(IF(C1>0,1,(IF(D1>0,1,0)))))

You can see Row 14 is not working

CodePudding user response：

Your formula is nested so first it checks IF B1>0 then if that is true it checks IF C1>0 otherwise it ends. Instead use OR to check if any of those are true.

=IF(OR(B1>0,C1>0,D1>0),"TRUE","FALSE")

Alternatively if your number might be equal to 0 or negative use ISNUMBER. =IF(OR(ISNUMBER(B1),ISNUMBER(C1),ISNUMBER(D1)),"TRUE","FALSE")

CodePudding user response：

If you just want to see if any of the cells is a number, you can use count:

=COUNT(B1:D1)>0

If you want to see if any of them contain a number greater than 0, try:

=OR(IF(ISNUMBER(B1:D1),B1:D1>0))

CodePudding user response：

Please check that formula,

=IFERROR(D1,IFERROR(C1,IFERROR(B1,0)))