incorrect output of if formula

[Seeker]

I have two cells with values which were macro auto copied from other cells by
copy - paste special – value, I have a =if(A1=A2,"yes","no") formula in third
cell to compare these two cells. From time to time the output of formula
gives inccorrect answer, but if I copy the first cell to the second cell
again manually using copy – paste function, the third cell gives the right
answer. Any idea why and how to correct this please?
Rgds

 

[Bernard Liengme]

Are you comparing real numbers (numbers with decimal places)?; if so, it is
possible they display the same but are very slightly different.
Try =if(ABS(A1-A2)<1E-10,"yes","no")
best wishes

 

[Jacob Skaria]

Check whether there is a space in one of the cells

Try the below..If it works then there is a space in one of the cells
=IF(TRIM(A1)=TRIM(A2),"yes","no")

or try
=CODE(A1)
If it returns a number insteadof #value there is a character there in one of
the cell.

If this post helps click Yes

 

[Jacob Skaria]

Oops. I thought you were having the formula incorrect when you have nothing
in it.

If text checkout for space.
Or
If numerics checkout for the exact value...


If this post helps click Yes

 

[Sheeloo]

This might happen when
1. you are comparing numbers which might be different due to the numbers
after the decimal. You may increase the display of numbers after the decimal
to see
2. there are spaces after the values

use
=Len(A1)
and
=Len(A2)
to see whether they are same or different

You may try
=IF(LEFT(A1,4)=LEFT(A2,4),"Yes","No")
to compare first four characters of the values

 

[Seeker]

Hi Bernard,
Thanks for your idea. Problems arised was nothing related to the decimal
places because I extract the figure before decimal for compariation.
Rgds

 

[Seeker]

Hi Jacob,
I don’t know how to use the =CODE(), but the trimming formula works. Since
pasted value most of the time (but not definite?) showing “the number in cell
is formatted as text or preceded by an apostropheâ€, by using =trim(), this
problem is removed and cells are in the same format.
Rgds