Forum Discussion

BaoLinhHa's avatar
BaoLinhHa
Copper Contributor
Jul 20, 2023

Formula shows wrong result

Hello everybody,  I created a spreadsheet.  Then, I use =SUM() / =SUBTOTAL() function to calculate the sum. But the result is not correct.    After that, I created a pivot table to re-calculate. ...
  • JoeUser2004's avatar
    JoeUser2004
    Jul 21, 2023

    BaoLinhHa  wrote:  ``correct number Excel should show is 29,999,999.99999996``

     

    I understand why you would think that.

     

    But even if the result in F5 were 29,999,999.99999996, Excel would display only 30,000,000.

     

    The reason is:  29,999,999.99999996 is 16 digits, and Excel displays only up to the first 15 significant digits, rounded.

     

    -----

    BaoLinhHa  wrote:  ``When i used other application to calculate, it returned not the same as Excel``

     

    Because not all applications are limited to displaying only up to 15 significant digits, rounded.

     

    And some applications work with decimal values (*), whereas Excel (and most applications) works with binary values.

     

    Note:  I use the word "decimal" to mean the digits 0 through 9, not the fractional digits after the decimal point, which we might call decimal places.

     

    However, even if we work with decimal values, we would expect the result to be more than 30,000,000.

     

    The reason is:

      3 *    952,380.952380952 =   2,857,142.857142856

    19 * 1,428,571.42857143   = 27,142,857.14285717

    2,857,142.857142856 + 27,142,857.14285717 =  30,000,000.000000026

     

    And again, Excel would display only 30,000,000 because 30,000,000.000000026 is 17 digits.

     

    -----

    BaoLinhHa  wrote:  ``I have a question: If the excel displays only first 15 significant digits of a number but if it is integer not decimal, because you know Excel can increase or decrease the number of decimal``

     

    I do not understand the question.  Please rephrase it if the following does not address your question.

     

    There are two important things to understand....

     

    .1  Excel stores a binary approximation of most decimal values that have fractional digits (digits after the decimal point).

     

    .2  Excel displays a decimal approximation of the binary value.  As noted above, the displayed decimal approximation is rounded to 15 significant digits.

     

    Because the binary value might be an approximation, the results of binary arithmetic often does not match exactly the results of the same decimal arithmetic that we might do manually or with a non-binary application (*).

     

    Moreover, the binary approximation of the same decimal fraction (digits after the decimal point) might vary depending on the magnitude of the value.  That is why, for example, 10.1 - 10 = 0.1 returns FALSE(!).  The binary approximation of 1/10 in 10.1 differs from the binary approximation of 0.1 by itself.

     

    Some people refer to that as binary arithmetic "error".  I prefer to call it an "anomaly".  It is a side-effect of the typical industry-standard choice (*) for representing numeric values.  It is not a defect.

     

    So, even if the values in E3 and E4 were the decimal values that Excel displays, the decimal approximation of their internal binary values might be different.

     

    Sometimes, we can see the difference.  For example (highlighting the displayed decimal fraction, unrounded; MOD(x,1) separates the binary fractional part from the binary value):

     

    =MOD(952380.952380952, 1) = 0.952380951959639

    =MOD(1428571.42857143, 1) = 0.428571430034935

     

    And even those are decimal approximations of the binary value.

     

    -----

     

    However, note that the binary values in C2:C23 and in E3 and E4 are not (the binary approximations of) the decimal values that Excel displays.

     

    Effectively, the Pivot Table copies the binary results of calculations, not their displayed decimal values.

     

    Consequently, for example:

     

    =MOD(E3, 1) = 0.952380952425301

    =MOD(E4, 1) = 0.428571428637952

     

    Compare with the MOD of the displayed values above.  The approximate differences are:

     

    E3:    0.000000000465661287307739

    E4:  -0.00000000139698386192322

     

    Consequently, the sum in C24 and F5 is not even 30,000,000.000000026, as we should expect based on the decimal arithmetic above.

     

    Instead, it is approximately 30,000,000.00000000745058059692383

     

    because INT(F5) is 30,000,000 and MOD(F5,1) is approximately 0.00000000745058059692383

     

    -----

     

    I apologize if that is TMI.  It is very complicated stuff.

     

    But you really do not need to understand it, beyond being aware that the problem exists.

     

    All you need to remember is....

     

    Whenever we expect a calculation to be accurate to some number of decimal places, explicitly round to that number of decimal places -- and not to an arbitrary number.

     

    I do not use or know much about Pivot Tables.  But I believe that is difficult to do in PTs.

     

    Perhaps HansVogelaar  or  SergeiBaklan  can  help you with that.

     

    -----

    PS: Really TMI....

     

    Another possible reason for different results in some applications is:  they might use a different internal binary representation of the decimal values.

     

    Every choice is a compromise in space, speed, and accuracy.

Resources