Csdrhrt

Hello to all Super Users!

I would like to ask about one interesting thing I found today related to sum function within rounding in excel. I have the following set of numbers (they are rounded to full decimal places - no other decimal numbers following in each of the numbers):

0.140495867768595,

0.173553719008264,

0.206611570247934,

0.076741440377804,

0.0826446280991736,

0.118063754427391,

0.063754427390791,

0.0814639905548997,

0.0566706021251476,

When I perform a simple sum function in excel result is equal to 1.0000000000000000 but when I put these numbers into calculator result is equal to 0.9999999999999999 (which is correct) -> my question, therefore, is there any way how to set the behavior of Excel to calculate this correctly and why is it calculating it wrong? Thank you all in advance for your suggestions!

Excel number values have 15 digits of precision. Everything after the 15th digit will displayed as a 0.

Microsoft support article

Workaround: If you want to show more than 16 significant digits, you need to enter the value as text, i.e. start with a single quote, then enter the value.

Excel stores 15 significant digits of precision. So that fifth value in your list -- 0.0826446280991736, is treated as 0.082644628099174 in the sum. Similarly for the eighth and ninth values in your list.

See 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/ for more information about how Excel stores numbers.

Excel only stores 15 significant digits of precision. Anything over 15 significant digits will be rounded.

Therefore, the result 0.9999999999999999 is rounded to 1.00000000000000, i.e. 1.

Input                     Sum (by Excel)           Sum (Actual)

-----------------------   ----------------------   -----------------------

0.140495867768595  [15]   0.140495867768595 [15]   0.140495867768595  [15]  

0.173553719008264  [15]   0.314049586776859 [15]   0.314049586776859  [15]  

0.206611570247934  [15]   0.520661157024793 [15]   0.520661157024793  [15]  

0.076741440377804  [14]   0.597402597402597 [15]   0.597402597402597  [15]  

0.0826446280991736 [15]   0.680047225501771 [15]   0.6800472255017706 [16]   

0.118063754427391  [15]   0.798110979929162 [15]   0.7981109799291616 [16]   

0.063754427390791  [14]   0.861865407319953 [15]   0.8618654073199526 [16]   

0.0814639905548997 [15]   0.943329397874852 [15]   0.9433293978748523 [16]   

0.0566706021251476 [15]   1.00000000000000  [15]   0.9999999999999999 [16]   

0.000000000000001  [ 1]   1.00000000000000  [15]   1.0000000000000009 [17]   <-- Bonus info



[ ] = number of significant digits

Bonus: Even if you add another 0.000000000000001 (15th decimal place), the result will still be 1.

Reference: https://en.wikipedia.org/wiki/Numeric_precision_in_Microsoft_Excel