excel copy and special paste (values) changes the real value

[matan2244]

i made cell A1 equal 1.123, cell B1 equal 1.22 and cell C1 is a formula: =A1-B1.
it shows the value 0.001.
then i copied cell C1 and made a special paste (values) in D1.
i expected that the value of D1 would be 0.001 just like C1. but it is different. excel made it 0.00099999999999989.
why did it happen? what can i do to fix it?

 

[matan2244]

try 2 to upload phothos.

 

[steve the fish]

I presume you mean 1.122. You should see that if you do this:

=C1=D1

that they are the same. Its just a floating point arithmetic error.

 

[matan2244]

it is not the same.
if i copy the formula and paste it as values to another file, and then try to compare it to 0.001 i get FALSE.

 

[steve the fish]

You said you copied the formula cell and then paste special the value in D1. I said try =C1=D1. They are the same. The formula is producing a floating point error. If you want to solve it wrap your formula with a round function:

=ROUND(A1-B1,3)

 

[matan2244]

then i tried copying it to another file. if you copy it to another file, when you compare a cell with 0.001 to a cell with the pasted cell you get a FALSE.
=ROUND() will not help me because i need a solution to many copy and pasting situations. sometimes the formula's solution is 3 digits, sometimes 10. i need a solution to my problem so i can paste the very same solution of my formula to another file.

 

[steve the fish]

The way a computer stores data means there are often these tiny rounding errors. There is no getting around them. You just need to know they potentially exist.