1

I have a table in Excel with two colymns: A and B. Column B, starting from the second row, takes a value from B column, one row higher and subtracts value from column A from one row higher from it.

332 406 053,41    |   3 300 000 000,00
0,00              |   2 967 593 946,59
0,00              |   2 967 593 946,59
0,00              |   2 967 593 946,59
0,00              |   2 967 593 946,59
0,00              |   2 967 593 946,59
0,00              |   2 967 593 946,59
0,00              |   2 967 593 946,59
0,00              |   2 967 593 946,59
110 802 017,80    |   2 967 593 946,59
110 802 017,80    |   2 856 791 928,79
110 802 017,80    |   2 745 989 910,99
0,00              |   2 635 187 893,19
585 597 309,59    |   2 635 187 893,19
0,00              |   2 049 590 583,60
0,00              |   2 049 590 583,60
0,00              |   2 049 590 583,60
0,00              |   2 049 590 583,60
0,00              |   2 049 590 583,60
0,00              |   2 049 590 583,60
0,00              |   2 049 590 583,60
0,00              |   2 049 590 583,60
0,00              |   2 049 590 583,60
0,00              |   2 049 590 583,60
0,00              |   2 049 590 583,60
0,00              |   2 049 590 583,60
0,00              |   2 049 590 583,60
0,00              |   2 049 590 583,60
0,00              |   2 049 590 583,60
0,00              |   2 049 590 583,60
0,00              |   2 049 590 583,60
0,00              |   2 049 590 583,60
0,00              |   2 049 590 583,60
0,00              |   2 049 590 583,60
0,00              |   2 049 590 583,60
0,00              |   2 049 590 583,60
55 634 374,57     |   2 049 590 583,60
55 634 374,57     |   1 993 956 209,03
55 634 374,57     |   1 938 321 834,46
55 634 374,57     |   1 882 687 459,88
55 634 374,57     |   1 827 053 085,31
55 634 374,57     |   1 771 418 710,74
55 634 374,57     |   1 715 784 336,17
55 634 374,57     |   1 660 149 961,60
55 634 374,57     |   1 604 515 587,03
55 634 374,57     |   1 548 881 212,46
55 634 374,57     |   1 493 246 837,89
55 634 374,57     |   1 437 612 463,32
55 634 374,57     |   1 381 978 088,75
55 634 374,57     |   1 326 343 714,17
55 634 374,57     |   1 270 709 339,60
55 634 374,57     |   1 215 074 965,03
55 634 374,57     |   1 159 440 590,46
55 634 374,57     |   1 103 806 215,89
55 634 374,57     |   1 048 171 841,32
55 634 374,57     |   992 537 466,75
55 634 374,57     |   936 903 092,18
55 634 374,57     |   881 268 717,61
55 634 374,57     |   825 634 343,04
769 999 968,47    |   769 999 968,47
0,00              |   -0,0000010729

enter image description here

Sum in column A is equal to a value in B1. However, in the end the last row of B isn't equal to 0. It's less than zero and is a float number.

How is that possible and how to get zero after all the substraction? I've done similar calculations previously with no problem.

Ans
  • 1,212
  • 1
  • 23
  • 51

1 Answers1

3

Bad news here: Excel uses an IEEE754 double precision floating point type for all numeric cells.

That gives you about 15 decimal significant figures of precision (never worse than this, and sometimes better).

Your numbers are around 12 significant figures and the number of incremental calculations you have here are such that you will push the accumulation of errors into significance.

There's not a lot you can do about this other than perhaps rounding the final result to, say, three decimal places, or revisiting the logic to reduce the depth of the calculation process.

For further details, see Is floating point math broken?

Bathsheba
  • 231,907
  • 34
  • 361
  • 483
  • There's a [KB article](https://support.microsoft.com/help/78113/) with more details on how, exactly, Excel uses floating point. – Jeroen Mostert Jan 22 '18 at 13:12
  • @Bathsheba Why then the very similar calculations just on another dataset don' tresult in this precision problem? – Ans Jan 22 '18 at 13:38
  • @Ans: most likely because the following applies: "Excel 97, however, introduced an optimization that attempts to correct for this problem. Should an addition or subtraction operation result in a value at or very close to zero, Excel 97 and later will compensate for any error introduced as a result of converting an operand to and from binary." But this is speculation; an exact answer would need the exact dataset in question. The precision problem is always there (unless you happen to use exactly representable values), it just isn't always visible. – Jeroen Mostert Jan 22 '18 at 13:50