PDA

View Full Version : [SOLVED] Non-VBA question : Using OR criteria in an IF statement



Iron Sheik
12-10-2013, 01:30 AM
Hi,

My brain is a bit fried at the moment and I can't seem to figure this one out (I know the solution is going to be simple. Soooooo embarrassed :o:).

The question is in two parts. I thought by breaking it down into the individual parts it would work and then I could combine it but no luck.

With the first part, I'm trying to search several cells with an IF statement using the OR function to give me a result but it's not working. I want the formula to search the selected cells in a particular row and return an answer. Here's what I've got for part 1:

=IF(OR(E5, H5, J5, L5 =""),1,0)

None of the cells are blank yet the value I get is "1". I thought the answer that should be returned is "0".



The second part also has the OR function in it but it's comparing dates to another cell which contains today's date. Here's what I've got for part 2:

=IF(OR(F5, I5, K5, M5 <P21),1,0)

All of the cells have dates in the year 2014 and cell P21 is defined as TODAY(). Again, the value I get is "1" where I think it should be "0".

The end result of this whole saga is that I want to combine both of these equations into one formula. I'm using 2003 version of Excel. Can this be done??

Too confused at the moment. :confused:

Any help is greatly appreciated.

Paul_Hossler
12-10-2013, 05:42 AM
OR(E5="", H5="", J5=""),



Paul

Bob Phillips
12-10-2013, 08:11 AM
You could also use

=--(COUNTA(E5,H5,J5,L5)<>4)

SamT
12-11-2013, 03:39 PM
=IF(OR(F5<P21, I5<P21, K5<P21, M5 <P21),1,0)

Assuming no Date includes Time
IF(SUM(F5,I5,K5,M5)<P21*4,1,0)
or
IF(AVE(F5,I5,K5,M5)<P21,1,0)

Iron Sheik
12-20-2013, 10:25 PM
Hello,

Thanks everyone for your thoughts. Problem solved. :thumb


xld, curious to know how your suggestion works. The double minus at the start of the equation has got me a bit confused --(COUNTA(... :think:

Bob Phillips
12-21-2013, 05:14 AM
The main part of the formula, COUNTA(E5,H5,J5,L5)<>4, will return TRUE or FALSE as a result. Your formula returned 1 or 0, so to get that I coerce it. One - will change it to -1 or 0, almost but not quite, the second - changes it to a positive, 1 or 0.

Aussiebear
12-21-2013, 04:46 PM
One - will change it to -1 or 0, almost but not quite, the second - changes it to a positive, 1 or 0.

But isn't it positive to start with?

Bob Phillips
12-21-2013, 06:46 PM
No, it is true or false to start with, the first minus changes it to negative 1 or 0, the second minus changes it to positive 1 or 0.