Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Nested Ages Plus or Minus (IF)
How about
=IF(ABS(A1-B1)<=1,"Yes","No") where A1 and B1 are the age fields. |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Calculating ages | Excel Discussion (Misc queries) | |||
calculating ages | Excel Worksheet Functions | |||
CHANGE TRAILING MINUS TO BRACKETS OR PRECEEDING MINUS | Excel Discussion (Misc queries) | |||
dates into ages | Excel Worksheet Functions | |||
how can i show two layers of plus/minus signs for nested groups | Excel Discussion (Misc queries) |