| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
formula gives 2 different answers
 helloI've got a formula which gives me a wrong answer on some occusions. The formula is below- =IF(A1=A2,same,diff) - if i type '1' in cell A1 and '1' in cell A2, it gives me the answer 'same' which is correct. - if i type 'a' in cell A1 and 'a' in cell A2, it gives me the answer 'same' which is correct. - if i type '1' in cell A1 and 'a' in cell A2, it gives me the answer 'diff' which is correct. - if i type 'a' in cell A1 and '1' in cell A2, it gives me the answer 'diff' which is correct. - if i type '1' in cell A1 and leave cell A2 BLANK, it gives me the answer 'diff' which is correct. - if i leave cell A1 BLANK and type in '1' in cell A2, it gives me the answer 'diff' which is correct. However! - if i type 'a' in cell A1 and leave cell A2 BLANK, it gives me the answer 'same' which is INCORRECT. - if i leave cell A1 BLANK and type in 'a' in cell A2, it gives me the answer 'same' which is INCORRECT. It gives me the wrong answer if i have a 'blank' with a 'letter'. Does anyone know why? I've copied the same formula onto a new spreadsheet but it gives me the correct answer. So it seems its something on the 1st sheet it doesn't like. I've also noticed if a type 'TRUE' or 'FALSE' in the 1st sheet it'll convert it to the value of 1 or 0. However on the 2nd sheet the values do not get converted. - not sure if this helps any1 thanks -- klee ------------------------------------------------------------------------ klee's Profile: http://www.excelforum.com/member.php...o&userid=30304 View this thread: http://www.excelforum.com/showthread...hreadid=499685 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
formula gives 2 different answers
I dunno...........aside from the fact that you typed the formula incorrectly
here in this post, (by not including the necessary quotes), it should be =IF(A1=A2,"same","diff") , the formula seems to work fine here in my XL97...........but if you type the same "number" in both cells and one is formatted as TEXT, the result will be "diff". Vaya con Dios, Chuck, CABGx3 "klee" wrote: helloI've got a formula which gives me a wrong answer on some occusions. The formula is below- =IF(A1=A2,same,diff) - if i type '1' in cell A1 and '1' in cell A2, it gives me the answer 'same' which is correct. - if i type 'a' in cell A1 and 'a' in cell A2, it gives me the answer 'same' which is correct. - if i type '1' in cell A1 and 'a' in cell A2, it gives me the answer 'diff' which is correct. - if i type 'a' in cell A1 and '1' in cell A2, it gives me the answer 'diff' which is correct. - if i type '1' in cell A1 and leave cell A2 BLANK, it gives me the answer 'diff' which is correct. - if i leave cell A1 BLANK and type in '1' in cell A2, it gives me the answer 'diff' which is correct. However! - if i type 'a' in cell A1 and leave cell A2 BLANK, it gives me the answer 'same' which is INCORRECT. - if i leave cell A1 BLANK and type in 'a' in cell A2, it gives me the answer 'same' which is INCORRECT. It gives me the wrong answer if i have a 'blank' with a 'letter'. Does anyone know why? I've copied the same formula onto a new spreadsheet but it gives me the correct answer. So it seems its something on the 1st sheet it doesn't like. I've also noticed if a type 'TRUE' or 'FALSE' in the 1st sheet it'll convert it to the value of 1 or 0. However on the 2nd sheet the values do not get converted. - not sure if this helps any1 thanks -- klee ------------------------------------------------------------------------ klee's Profile: http://www.excelforum.com/member.php...o&userid=30304 View this thread: http://www.excelforum.com/showthread...hreadid=499685 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
formula gives 2 different answers
Check ToolsOptionsTransition, and you will probably find that the
'Transition formula evaluation' box is checked. Clear it. -- HTH Bob Phillips (remove nothere from email address if mailing direct) "klee" wrote in message ... helloI've got a formula which gives me a wrong answer on some occusions. The formula is below- =IF(A1=A2,same,diff) - if i type '1' in cell A1 and '1' in cell A2, it gives me the answer 'same' which is correct. - if i type 'a' in cell A1 and 'a' in cell A2, it gives me the answer 'same' which is correct. - if i type '1' in cell A1 and 'a' in cell A2, it gives me the answer 'diff' which is correct. - if i type 'a' in cell A1 and '1' in cell A2, it gives me the answer 'diff' which is correct. - if i type '1' in cell A1 and leave cell A2 BLANK, it gives me the answer 'diff' which is correct. - if i leave cell A1 BLANK and type in '1' in cell A2, it gives me the answer 'diff' which is correct. However! - if i type 'a' in cell A1 and leave cell A2 BLANK, it gives me the answer 'same' which is INCORRECT. - if i leave cell A1 BLANK and type in 'a' in cell A2, it gives me the answer 'same' which is INCORRECT. It gives me the wrong answer if i have a 'blank' with a 'letter'. Does anyone know why? I've copied the same formula onto a new spreadsheet but it gives me the correct answer. So it seems its something on the 1st sheet it doesn't like. I've also noticed if a type 'TRUE' or 'FALSE' in the 1st sheet it'll convert it to the value of 1 or 0. However on the 2nd sheet the values do not get converted. - not sure if this helps any1 thanks -- klee ------------------------------------------------------------------------ klee's Profile: http://www.excelforum.com/member.php...o&userid=30304 View this thread: http://www.excelforum.com/showthread...hreadid=499685 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
formula gives 2 different answers
thanks thats working now  thanks for your quick reply!Bob Phillips Wrote: Check ToolsOptionsTransition, and you will probably find that the 'Transition formula evaluation' box is checked. Clear it. -- HTH Bob Phillips (remove nothere from email address if mailing direct) "klee" wrote in message ... helloI've got a formula which gives me a wrong answer on some occusions. The formula is below- =IF(A1=A2,same,diff) - if i type '1' in cell A1 and '1' in cell A2, it gives me the answer 'same' which is correct. - if i type 'a' in cell A1 and 'a' in cell A2, it gives me the answer 'same' which is correct. - if i type '1' in cell A1 and 'a' in cell A2, it gives me the answer 'diff' which is correct. - if i type 'a' in cell A1 and '1' in cell A2, it gives me the answer 'diff' which is correct. - if i type '1' in cell A1 and leave cell A2 BLANK, it gives me the answer 'diff' which is correct. - if i leave cell A1 BLANK and type in '1' in cell A2, it gives me the answer 'diff' which is correct. However! - if i type 'a' in cell A1 and leave cell A2 BLANK, it gives me the answer 'same' which is INCORRECT. - if i leave cell A1 BLANK and type in 'a' in cell A2, it gives me the answer 'same' which is INCORRECT. It gives me the wrong answer if i have a 'blank' with a 'letter'. Does anyone know why? I've copied the same formula onto a new spreadsheet but it gives me the correct answer. So it seems its something on the 1st sheet it doesn't like. I've also noticed if a type 'TRUE' or 'FALSE' in the 1st sheet it'll convert it to the value of 1 or 0. However on the 2nd sheet the values do not get converted. - not sure if this helps any1 thanks -- klee ------------------------------------------------------------------------ klee's Profile: http://www.excelforum.com/member.php...o&userid=30304 View this thread: http://www.excelforum.com/showthread...hreadid=499685 -- klee ------------------------------------------------------------------------ klee's Profile: http://www.excelforum.com/member.php...o&userid=30304 View this thread: http://www.excelforum.com/showthread...hreadid=499685 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Is it possible? | Excel Worksheet Functions | |||
| Match then lookup | Excel Worksheet Functions | |||
| adding row to forumla | Excel Discussion (Misc queries) | |||
| Recalculating Formula - Getting the wrong answers | Excel Discussion (Misc queries) | |||
| Creating a check mark box | Setting up and Configuration of Excel | |||
Linear Mode
