Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#4
|
|||
|
|||
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 Quote:
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
macro sheet name conflicts | Excel Worksheet Functions | |||
MATCH FUNCTION AND SERIAL DATE CONFLICTS | Excel Worksheet Functions | |||
Save conflicts | Excel Discussion (Misc queries) | |||
Excel Option Conflicts | Excel Discussion (Misc queries) | |||
Excel Option Conflicts | Excel Discussion (Misc queries) |