Forum Discussion
Sum of 2 point precision decimals appears to produce a floating point error
Hello all, I have discovered, what seems to me to be a bug in Excel, and I was wondering if anybody here could offer a solution/workaround. I have attached a spreadsheet to illustrate the phenome...
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.
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.