Nested Ages Plus or Minus (IF)
 

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?

Nested Ages Plus or Minus (IF)
 

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

"Douglas Eckert" wrote in message
...
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?

Nested Ages Plus or Minus (IF)
 

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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Douglas Eckert" wrote in message
...
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?

Nested Ages Plus or Minus (IF)
 

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

--
---
HTH

Bob

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



"Douglas Eckert" wrote in message
...
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?

Nested Ages Plus or Minus (IF)
 

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

Nested Ages Plus or Minus (IF)
 

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"?

"Don Guillett" wrote:


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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Douglas Eckert" wrote in message
...
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?

Nested Ages Plus or Minus (IF)
 

David Biddulph: Thanks.

Doug Eckert

"David Biddulph" wrote:

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

"Douglas Eckert" wrote in message
...
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?

Nested Ages Plus or Minus (IF)
 

Bob: Thanks.

DOUG

"Bob Phillips" wrote:

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

--
---
HTH

Bob

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



"Douglas Eckert" wrote in message
...
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?

Nested Ages Plus or Minus (IF)
 

Dave O: Thanks.

DOUG

"Dave O" wrote:

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