Imprecision of the column sum result

1

Environment: Excel 2013 Example: When adding the values of a column with 1110 lines, the result obtained is 302.204.548,38 If you add the value of 249.998.78 from line 1111 of the same column, you should get 302.454.547,16 but instead of this value the result is 302.454.547.159999

Can anyone help me overcome this inaccuracy? Thank you in advance for the help.

Anthony Jesus

    
asked by anonymous 10.05.2016 / 17:56

1 answer

2

Look, this is not really a bug, but a feature of how floating-point numbers (with decimals) are stored and operated in computer memory.

Because numbers are binary encoded and have limited precision, there are fractional numbers that do not have an exact binary match. When the value resulting from a mathematical operation results in one of these numbers, a difference appears that would not exist in "pencil and paper" mathematics in base 10.

A much simpler example than yours would be:

   =1*(0,5-0,4-0,1)

Type this in any Excel cell and you will receive a very small value, but different from 0, which would be expected.

Microsoft has two articles explaining this and suggesting fixes:

10.05.2016 / 18:45