Sum of 2 point precision decimals appears to produce a floating point error

[Cormac Biggar]

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

[#2]

Hi Cormac,

That's by design, the nature of floating point calculations. You may check

https://www.microsoft.com/en-us/microsoft-365/blog/2008/04/10/understanding-floating-point-precision...

https://support.microsoft.com/en-us/help/78113/floating-point-arithmetic-may-give-inaccurate-results...

and in other places

[#3]

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.

[#4]

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.

[#5]

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?

[#6]

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.