CODEWITHSUNDEEP
excel
Amir
Amir

Reputation: 1683

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

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

enter image description here

Upvotes: 0

Views: 904

Answers (3)

R.bora
R.bora

Reputation: 59

Please check that formula,

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

Upvotes: 0

Tom Sharpe
Tom Sharpe

Reputation: 34210

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

enter image description here

Upvotes: 1

Basbadger
Basbadger

Reputation: 234

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

Upvotes: 1

Related Questions