Excel Paste Special Error – How to Fix Number Only Paste Issues

copy/pastemicrosoft excel

Excel (Office 365) is making a strange error when pasting numbers only:

cell A1 value = 1727979.28
cell A2 value = 1678319.54
cell A3 formula = (A2 – A1) -> result = -49659.74

if I select&copy A3 and paste special (values only) to A4 the value in A4 = -49659.7399999999 ?!?

Is this expected bahaviour or some setting I can change?

Best Answer

That's not a mistake. It's all about how Excel stores such numbers, in this case in point float number variables, which can be seen as Scientific Notation and a division of numbers. This division is an aproximation of the real number, in you case 49659.74 so it's going to get stored as a compact division that can generate a "string" of numbers like that and then it's going to place the decimal point, that's where the scientific notation comes around.

For a machine -49659.74 and -49659.7399999999, are pretty much the same. And for most cases it's good enough. If you need to use only two decimals, sorround your formula with =round(YOUR_FORMULA;2) this way your number will get rounded to two decimal.

It's addressed by Microsoft Here How Excel handles decimal number.

Cheers

Related Question