Forum Discussion
Sum of 2 point precision decimals appears to produce a floating point error
thanks for your response but I guess I didn't express myself clearly enough.
I rounded every number to 2 decimal places and still get an imprecise answer.
I am looking for a way to force numbers to 2 point precision and the round() formula isn't doing it.
I am well familiar with floating point arithmetic. I however thought that excel allows you to turns floats into decimals via the round() formula.
I'm not an expert in floating point arithmetic, just based on experience SUM(ROUND(<array>,2)) is not necessary equal to ROUND(SUM(<array>),2). Well known example
=1*(0.5-0.4-0.1)
is not equal to zero, as well as
=ROUND(1,1)*(ROUND(0.5,1)-ROUND(0.4,1)-ROUND(0.1,1))
also is not zero. Binary representation of each number in above will be the same if you round it or not, floating point will take effect on calculating the result.
We may consider that as the bug, or as the nature of floating point, but that how it works.
Do I understand correctly that rounding values in excel only represents them as decimals, whereas they are continued to be stored and computed as floating points?
Does this also mean that my result in cell E78280 of worksheet 'excel generated series' is of 2 point precision by pure coincidence?So the only solution is to round every sum even if the constituents have already been rounded?
Cormac,
Perhaps the explanation is on first article from links before. If you round 0.1 it'll be the same 0.1, but the binary representation of it will be 0.0001100110011 where 1100 repeats infinitely. Thus Excel collects error on it around 2.8e-17 when store and that affects summary result in calculations.
I guess yes, the solution is to round not only intermediate results but final ones as well. At least that's how our accountant do.