Nested Ages Plus or Minus (IF)

[Guest]

I used the following formula to ask MS Excel to compare two age fields to see
if they are within one year (plus or minus) of matching each other. (It
returned all "NO" answers, which was incorrect).

=IF(((B2=C2)*OR(B2=C2+1)*OR(B2=C2-1)*OR(B2+1=C2)*OR(B2-1=C2)),"YES","NO")

'Suggestions, please?

 

[David Biddulph]

You ought to look in Excel help to find the syntax for the functions you
have used. For the OR function to be useful, it needs more than one
argument. As it is not possible for B2 to be equal to C2 and C2+1 and C2-1,
at least 2 out of those 3 are going to be FALSE (and similarly with your C2
conditions at least 1 will be FALSE). [Further to that, why are you testing
for B2+1=C2 when you've already tested for B2=C2-1, and similarly why test
for B2-1=C2 when you've already tested for B2=C2+1?]
When you multiply anything by FALSE you will get a zero (equivalent to
FALSE), so yes, you are right, you will always get "NO" as the answer.

Try changing the syntax to
=IF(OR(condition_a,condition_b,condition_c),"YES","NO")

Perhaps you could simplify it further to
=IF(AND(B2<=C2+1,B2>=C2-1),"YES","NO")

 

[Don Guillett]

Test this
=IF(OR(B2=C2,B2=C2+1,B2=C2-1),"y","N")

 

[Bob Phillips]

=IF(B2<C2,IF(DATEDIF(B2+1,C2,"Y")=0,"YES",""),IF(DATEDIF(C2+1,C2,"Y")=0,"YES",""))

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Dave O]

How about
=IF(ABS(A1-B1)<=1,"Yes","No")
where A1 and B1 are the age fields.

 

[Guest]

Don: MARVELOUS! Thank you very much.

Doug Eckert

PS, Do you know a formula which says, "if two out of three cells match, then
"YES"?

 

[Guest]

David Biddulph: Thanks.

Doug Eckert

You ought to look in Excel help to find the syntax for the functions you
have used. For the OR function to be useful, it needs more than one
argument. As it is not possible for B2 to be equal to C2 and C2+1 and C2-1,
at least 2 out of those 3 are going to be FALSE (and similarly with your C2
conditions at least 1 will be FALSE). [Further to that, why are you testing
for B2+1=C2 when you've already tested for B2=C2-1, and similarly why test
for B2-1=C2 when you've already tested for B2=C2+1?]
When you multiply anything by FALSE you will get a zero (equivalent to
FALSE), so yes, you are right, you will always get "NO" as the answer.

Try changing the syntax to
=IF(OR(condition_a,condition_b,condition_c),"YES","NO")

Perhaps you could simplify it further to
=IF(AND(B2<=C2+1,B2>=C2-1),"YES","NO")
--
David Biddulph

I used the following formula to ask MS Excel to compare two age fields to
see
if they are within one year (plus or minus) of matching each other. (It
returned all "NO" answers, which was incorrect).

=IF(((B2=C2)*OR(B2=C2+1)*OR(B2=C2-1)*OR(B2+1=C2)*OR(B2-1=C2)),"YES","NO")

'Suggestions, please?

 

[Guest]

Bob: Thanks.

DOUG

=IF(B2<C2,IF(DATEDIF(B2+1,C2,"Y")=0,"YES",""),IF(DATEDIF(C2+1,C2,"Y")=0,"YES",""))

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Guest]

Dave O: Thanks.

DOUG