formula gives 2 different answers

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

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:

>
> 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
>
>

Check Tools>Options>Transition, 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" <klee.21f30z_1136893502.1665@excelforum-nospam.com> wrote in message
news:klee.21f30z_1136893502.1665@excelforum-nospam.com...
>
> 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
>

thanks thats working now thanks for your quick reply!

Originally Posted by Bob Phillips

Check Tools>Options>Transition, 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" <klee.21f30z_1136893502.1665@excelforum-nospam.com> wrote in message
news:klee.21f30z_1136893502.1665@excelforum-nospam.com...
>
> 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
>