Calculation issues with prime number calculation

Microsoft

Hi all,

 

I have identified issue with simple calculation.
If you SUM following this exact order (29/21)+(27.5/21)+(21/21)+(21/21*-1)+(29/21*-1)+(3.2/21*-1)+(0.25/21)+(27.5/21*-1)+(0.25/21*-1)+(3.2/21) the result would not be 0, but 0.0000000000000002775557561562890.

 

29 is prime number and most probably it is causing the problem - I guess when it's in the middle of the calculation the result is indefinite number and afterwards it cannot cope with second indefinite number.

 

Additionally, if you switch the places of the numbers the result changes and if you put the positive and the negative numbers next to each other the result will be 0.

Same behavior is experienced when Pivot is created.

 

I am attaching the file with the tests.
Column A are the numbers used,
Column B are the calculated result
Column C are the exact numbers, instead of calculation
Column D are the same numbers, but sorted.
Row 12 are the totals.

 

The pivots below show the results when the numbers are sorted and when they're not.

 

Thank you for your time.

Ivan Lyubenov

E.: v-ivlyub@microsoft.com

M.: +359 882 474 305

4 Replies

@IvanVonLyubenov Excel doesn't handle more than 14 or 15 decimals. So , your number = 0

@Riny_van_Eekelen hello, thank you for the quick response.

Usually that's the situation, but try to explain it to the if(sum(TheNumbersAbove)=0,"OK","KO") :D

Regards

@IvanVonLyubenov 

Did that. The result is indeed 2.77556E-16. As I understand from your comment, it should be -zero-. But, as said Excel doesn't handle more than 15 decimals. Beyond that, it often goes wrong. I believe its called a floating point error and there's noting you can do about it. Wrap your formula in ROUND (<formula>,15) and the result will be -zero-.

 

@IvanVonLyubenov 

More about floating point error is here https://www.microsoft.com/en-us/microsoft-365/blog/2008/04/10/understanding-floating-point-precision....

Resulting 2.8*e-17 is not the result of the calculation, that's approximation which Excel does presenting as finite number the value which is in theory infinite.

If try classical =1*(.5-.4-.1) it also returns exactly the same 2.8*e-17. As any other formula where floating point error appears.

 

In addition, if add data to data model creating the PivotTable, sum will be the same 2.8*e-17 independently of sorting. But that's the story of how different calc engines work on same data.