Using IF and OR

D

Dustin B

I am trying to use if and or statement together and cannot get the syntax
right. I know I must be missing something quite obvious here. What I am
trying to do is mark a cell with a 0 or 1 if cell K2 =
11,or13,or14,or17,or18,or20,or21,or23. Can someone throw me a bone on this
one? The help file in exel does not show syntax for using if and or together
and I guess that is what I am messing up.

Thanks.
 
D

Dave Peterson

One way:
=if(OR(K2={11,13,14,17,18,20,21,23}),1,0)

Another way without the =if()

=--OR(K2={11,13,14,17,18,20,21,23})

The =or() function returns True or False. The first - sign converts false to 0
and true to -1. The second - sign doesn't change the 0, and converts the -1 to
+1.
 
D

David Biddulph

It's easy enough, in that the condition test in the IF statement might
either be a simple test, such as K2=11, or it can be any other formula which
returns a TRUE or FALSE, so you merely replace the test by the OR function.
=IF(OR(K2=11,K2=13,K2=14,K2=17,K2=18,K2=20,K2=21,K2=23),1,0)

You can shorten this to =IF(OR(K2={11,13,14,17,18,20,21,23}),1,0)
and because TRUE and FALSE are evaluated as 1 and 0 respectively you can
omit the IF and use
=--OR(K2={11,13,14,17,18,20,21,23})
where the double unary minus before the OR coerces the TRUE or FALSE to a
numerical value.
 
Top