Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
sorting atuofilter incorrect output | Excel Discussion (Misc queries) | |||
formula output | Excel Worksheet Functions | |||
How do I use a referenced cell's formula instead of formula output? | Excel Worksheet Functions | |||
add text to end of formula output | Excel Discussion (Misc queries) | |||
formula for custom output | Excel Discussion (Misc queries) |