Logical Formula Help

  • Thread starter Thread starter dls61721
  • Start date Start date
D

dls61721

I have a need for a working formula that has and OR and an AND statement.
All I can come up with is the following, but of course it does not work.
Thanks!
=IF(OR(C2#OR#D2#OR#E2,>4/1/2010#AND#<4/1/2013,1,""))

Doug
 
Doug,

=IF(SUMPRODUCT((C2:E2>DATEVALUE("4/1/2010"))*(C2:E2<DATEVALUE("4/1/2013")))>0,1,"")

HTH,
Bernie
MS Excel MVP
 
dls61721 said:
I have a need for a working formula that has and OR and an AND statement. [....]
=IF(OR(C2#OR#D2#OR#E2,>4/1/2010#AND#<4/1/2013,1,""))
Click to expand...

First, in Excel, OR and AND take the form of a function, like everything
else. So the general form of such an IF expression would be:

=IF(OR(AND(this,that),AND(this,that)), 1, "")

Second, however, this might be easier to express with a different function,
namely:

=IF(SUMPRODUCT((--"4/1/2010"<C2:E2)*(C2:E2<--"4/1/2013")), 1, "")

The multiplication ("*") is effectively an AND operation in this context,
and SUMPRODUCT is effectively an OR. The form --"4/1/2010" causes the date
string to be treated as a date number. Note: --"4/1/2010" is sufficient
for your own use. DATE(2010,4,1) would be better if others might use your
formulas, especially with different Regional and Language Options in the
Control Panel.

Note that this is equivalent to the following pseudo-expression (i.e. not as
you would write it in Excel):

IF((4/1/2010<C2 and C2<4/1/2013) or (4/1/2010<D2 and D2<4/1/2010) etc, 1,
"")

I assume that is what you wanted. If not, please be more specific.


----- original message -----
 
The way you use And and Or is:
=if(or(and(c2>date(2010,4,1),c2<date(2013,4,1)),and(d2>date(2010,4,1),d2<date(2013,4,1)),and(e2>date(2010,4,1),e2<date(2013,4,1))))

Regards,
Fred
 
Thank everyone for their assistance in this matter. It works fine and saved
me several hours of work:)
Doug
 
Back
Top