incorrect output of if formula
 

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

incorrect output of if formula
 

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
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email


"Seeker" wrote in message
...
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

incorrect output of if formula
 

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


"Seeker" wrote:

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

incorrect output of if formula
 

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


"Jacob Skaria" wrote:

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


"Seeker" wrote:

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

incorrect output of if formula
 

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" wrote:

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

incorrect output of if formula
 

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

"Bernard Liengme" wrote:

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
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email


"Seeker" wrote in message
...
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

incorrect output of if formula
 

Hi Jacob,
I dont 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


"Jacob Skaria" wrote:

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


"Seeker" wrote:

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