Function Conflicts with an If/Then Statement
 

I am trying to finalize a spreadsheet I have developed, but am having trouble with one of the final cell functions. The function is to compare two cells. Each cell will have a 0, 1 or 2. If the cells are identical then a 0 should appear in a third cell. If one cell is a 0 and the other is a 1 or 2 then a 1 should appear in the third cell. If one cell is a 1 and the other is a 2 then a 0 should appear in a third cell. This is what i would like, but don't know how to write as one cell function:
"If A2 is equal to 0 and A72 is equal to 0 then 0
If A2 is greater than 0 and A72 is greater than 0 then 0
If A2 is equal to 0 and A72 is greater than 0 then 1
If A2 is greater than 0 and A72 is equal to 0 then 1"


the problem is I can't seem to get it right. I always have a conflict where the program says something is false when it is true or true when it is false.

Here is what I have written:
=IF(A2=A72,0)+IF(A20+A720,0)+IF(A2=0+A720,1)+IF (A20+A72=0,1)

Thanks if you have any suggestions
Marshall

Function Conflicts with an If/Then Statement
 

=IF(OR(AND(A2=0,A72=0),AND(A20,A720)),0,1)

Regards,
Stefi

€žmcowan3€� ezt Ã*rta:


I am trying to finalize a spreadsheet I have developed, but am having
trouble with one of the final cell functions. The function is to
compare two cells. Each cell will have a 0, 1 or 2. If the cells are
identical then a 0 should appear in a third cell. If one cell is a 0
and the other is a 1 or 2 then a 1 should appear in the third cell. If
one cell is a 1 and the other is a 2 then a 0 should appear in a third
cell. This is what i would like, but don't know how to write as one
cell function:
"If A2 is equal to 0 and A72 is equal to 0 then 0
If A2 is greater than 0 and A72 is greater than 0 then 0
If A2 is equal to 0 and A72 is greater than 0 then 1
If A2 is greater than 0 and A72 is equal to 0 then 1"


the problem is I can't seem to get it right. I always have a conflict
where the program says something is false when it is true or true when
it is false.

Here is what I have written:
=IF(A2=A72,0)+IF(A20+A720,0)+IF(A2=0+A720,1)+IF (A20+A72=0,1)

Thanks if you have any suggestions
Marshall




--
mcowan3

Function Conflicts with an If/Then Statement
 

You don't say what you want when either of the numbers is less than 0 so try
either this:

=IF(A20,if(A720,0,1),IF(A720,1,0))
(which uses <=0 as the other case)

or this

=if(A2=0,if(a72=0,0,1),if(a72=0,1,0))
(which use <0 as the other case)

regards
Charles
_________________________________________
UK Cambridge XL Users Conference 29-30 Nov
http://www.exceluserconference.com/UKEUC.html

"mcowan3" wrote in message
...

I am trying to finalize a spreadsheet I have developed, but am having
trouble with one of the final cell functions. The function is to
compare two cells. Each cell will have a 0, 1 or 2. If the cells are
identical then a 0 should appear in a third cell. If one cell is a 0
and the other is a 1 or 2 then a 1 should appear in the third cell. If
one cell is a 1 and the other is a 2 then a 0 should appear in a third
cell. This is what i would like, but don't know how to write as one
cell function:
"If A2 is equal to 0 and A72 is equal to 0 then 0
If A2 is greater than 0 and A72 is greater than 0 then 0
If A2 is equal to 0 and A72 is greater than 0 then 1
If A2 is greater than 0 and A72 is equal to 0 then 1"


the problem is I can't seem to get it right. I always have a conflict
where the program says something is false when it is true or true when
it is false.

Here is what I have written:
=IF(A2=A72,0)+IF(A20+A720,0)+IF(A2=0+A720,1)+IF (A20+A72=0,1)

Thanks if you have any suggestions
Marshall




--
mcowan3

Thanks a lot, this worked exactly as I wanted. I have been banging my head against the wall for a while, making it much more complex than needed resulting in even more confusion. Simple is better.
Thanks again
Marshall