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 phenomenon.
In brief: my spreadsheet returns either a floating point decimal or 8 point precision decimal when evaluating a large series of 2 point precision decimals.
Specifically:
In the worksheet called 'imported series' you can find a large array of decimals defined as 2 point precision in our database which I pasted into column B. In column C I check whether they are equal to round(val,2), which evaluates as TRUE for every single value. The sum of these numbers however in cell B78280 clearly returns a value that is not a 2 point precision decimal.
Similarly, in column E, I actually round each value to 2 decimals in Excel, but the problem inexplicably still persists in the sum of this column, which you can find in cell E78280.
I tried to reproduce this error in an excel generated series in the work sheet titled 'excel generated series'. In this worksheet, column B is a series of 2 point decimals growing by 0.01 in each row. Similarly the sum of this column in cell B78280 returns a value that is not a 2 point precision decimal.
However really strangely, when forcing each of these value to 2 point precision in column E, the column sum total suddenly returns a 2 point precision decimal.
I don't know enough about computer engineering but to me the only logical explanation appears to be a bug. Any advice perhaps?
Thanks in advance
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.
