Firstly I do not want to round my numbers, I am using base 10 figures so excel should have no bother doing simple add and subtract.
A| 665.110000000000000000000000000000
B| 4.990000000000000000000000000000
C| =a1+b1
D| 670.790000000000000000000000000000
E| 1,335.890000000000000000000000000000
F| =e1-(c1+d1)
I should get -5 however F1 returns
-4.999999999999770000000000000000
It is a financial document therefore I want to avoid rounding, more to the point. Why should I need to?
I've put cell formatting to Currency with 2 decimal placed and I will get the correct display value but for all calculations I will still get -4.9999…7 which leads to further errors with conditional formatting etc.
Best Answer
The problem is due to the basic hardware of almost all computers. They are based on binary systems so they don't count from 0 to 9, instead they just count 0 and 1, or base 2.
This is fine unless as you've discovered you want high precision decimal numbers. Computers can only count in terms of fractions that are powers of a half, i.e. 1/2, 1/4, 1/8, 1/16, 1/32 etc. There is no easy way for a computer to represent some numbers such as 1/3 or 4.99 perfectly accurately without giving up a lot of performance.
Excel is quite good at hiding this fact, it's only if you turn on 20 or so decimal places do you see this error. It does this by rounding, but only rounding when the result is displayed.
You say this is for financial calculations, do you really need the final result displayed to 20 decimal places? Or do you just need to be sure that the calculation is done with enough precision that rounding errors won't occur. If it's the latter you may just have to trust that excel has been doing it's job for a few years for countless financial organizations, without problem. And that you need not worry about those rounding errors causing you problems.