MS Excel formula

Boomtownrat81

Hi all,
I am trying to get up to speed on excel and I am doing okay but cant for the life of me sort this one out. When using the "or" function in conjunction with "if" I want to know how to search a column range for a variety of numbers, for example, searching cells B9 to B17 for the numbers 1,3, or 5 and if they are present then true, if not then false.. This is my formula;

=IF(OR(B9:B17)(1,3,5)),"True","False")

Where am I going wrong?
Thanks

Overheal

Well quite plainly, why does your OR function have 2 calls?

It should be OR() not OR()()

So you need to rephrase your 2 calls as one call. Both are half right. (Range==val1,range==val2,...)

Boomtownrat81

Thanks for the reply.
Could you write the correct formula if possible please?
Thanks again

Lu Tze

Boomtownrat81 wrote: »

Thanks for the reply.
Could you write the correct formula if possible please?
Thanks again

I dont think you can do an if statement for a range like that, if you put this statement in C9 and drag down it will give you a result for each of the cells in the range in the B column.

=IF(OR(B9=1,B9=3,B9=5),"True","False")

Not sure if that helps

lrishJoe

To test an individual cell for values 1, 3, or 5 you could do:

=IF(OR(B9=1,B9=3,B9=5),"True","False")

Or to test the range as you asked you could do:

=IF(COUNTIF(B9:B17,1)+COUNTIF(B9:B17,3)+COUNTIF(B9:B17,5)>0,"True","False")

But I'm not sure either if these is really what you want; maybe you can state the question more explicitly?

Edser

You don't need the IF. Try


=OR($B$9:$B$17 = 1, $B$9:$B$17 = 3, $B$9:$B$17 = 5)

or

=OR(B9 = 1, B9 = 3, B9 = 5)


Note, you can use the range with absolute values or simple cell values and fill down.


Ed

Boomtownrat81

lrishJoe wrote: »

To test an individual cell for values 1, 3, or 5 you could do:

=IF(OR(B9=1,B9=3,B9=5),"True","False")

Or to test the range as you asked you could do:

=IF(COUNTIF(B9:B17,1)+COUNTIF(B9:B17,3)+COUNTIF(B9:B17,5)>0,"True","False")

But I'm not sure either if these is really what you want; maybe you can state the question more explicitly?

The countif formula above did the job perfectly. Thanks very much

lrishJoe

Boomtownrat81 wrote: »

The countif formula above did the job perfectly. Thanks very much

You're welcome!