Forum Discussion
Sum of 2 point precision decimals appears to produce a floating point error
Hi Cormac,
That's by design, the nature of floating point calculations. You may check
and in other places
- Hi Sergei,
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?
