|
formula gives 2 different answers
:confused: hello I'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 |
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: :confused: hello I'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 |
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 ... :confused: hello I'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 |
formula gives 2 different answers
thanks thats working now :cool: 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 ... :confused: hello I'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 |
| All times are GMT +1. The time now is 05:59 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com