Formula shows wrong result

[BaoLinhHa]

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. But unfortunately, it's still not correct. 

Here is the picture I capture from the pivot table:

So that I post this post to need some help to resolve this problem. 

Is this a bug of Excel ?

I am using Excel 365. 

Thanks for watching! 

(I attached the data file bellow)

[#2]

@BaoLinhHa 

I don't see the formula =SUM() / =SUBTOTAL() anywhere in your posted spreadsheet (the one I downloaded). It's an odd formula, at best, to begin with.

The Pivot Table and a comparable pair of SUMIF formulas deliver the same results

What's the alleged error?

For that matter, what does this data represent?

Excel does have limits in terms of decimal places, degree of precision with really large (and probably really small) numbers.

As I've said before to folks wondering if they--out of the millions who are using Excel around the world--just happen to have been THE one to discover a major bug in Excel, consider the odds. It's far more likely, that the error is somewhere in your own expectation, your data, your use of Excel. Excel has pretty well proven itself over the years to be capable of basic addition and subtraction, multiplication and division.

[#3]

Dear sir,

Please look at the area I squared it in red thoroughly before commenting.
The correct number Excel should show is 29,999,999.99999996 .

Thanks!

[#4]

@BaoLinhHa 

No, the zeros at the end of the second value in the pivot table 27,142,857.142857100 are a result of the way Excel handles numbers. Excel displays only the first 15 significant digits of a number, any digits after that are replaced with zeros. If you use the Windows Calculator to calculate the value, you get 27142857.14285717. The grand total is really 30000000

[#5]

@BaoLinhHa 

Please look at the area I squared it in red thoroughly before commenting.
The correct number Excel should show is 29,999,999.99999996 .

I had looked at it. This is your first explanation of what you were expecting and what you were identifying as the "error."

I'm grateful to @Hans Vogelaar for having identified the specific limit in Excel when it comes to numbers with more than 15 significant digits.

[#6]

When i used other application to calculate, it returned not the same as Excel sir :D . You can import my file to database and select from it to calculate. The result is not the same as Excel.

[#7]

But 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 :D

[#8]

@BaoLinhHa Calculations in Excel may contain tiny rounding errors. I hope that @Joe User will read this - he can explain it much better than I can.

[#9]

@BaoLinhHa 

Yes, @Joe User will explain floating point error much better. I'd only like to mention that's not Excel specific. If you do the same in Google Sheets, and I guess in many other spreadsheet apps, you have exactly the same result. Number of kept bits is limited.

[#10 · Accepted solution]

@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 @Hans Vogelaar  or  @Sergei Baklan  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.

[#11]

Not TMI at all. An excellent and useful explanation. Thanks!!