Forum Discussion
Calculation issues with prime number calculation
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") 😄
RegardsMore about floating point error is here https://www.microsoft.com/en-us/microsoft-365/blog/2008/04/10/understanding-floating-point-precision-aka-why-does-excel-give-me-seemingly-wrong-answers/.
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.
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-.
