Forum Discussion
Calculation error
Hello Joe User,
Thanks for your reply's, don't know why I received your request (13 times) for concrete examples.
You clarified one thing for me,
"The actual limitation is the internal binary precision, not a decimal precision.
Second, the formatting limit is 15 significant digits, not decimal places.
For example, 12345.6789012345 has only 10 decimal places, but it has 15 significant digits, just as 0.123456789012345 does."
That is the point where my calculations go wrong, but since I added a check, which notify’ s me if there is a difference in the total horizontal compared to the total vertical. It makes it easier for me to rule out any problems.
I have added the example you requested. Hope this clarifies it for you.
Hello Joe User,
Thanks for your reply's, don't know why I received your request (13 times) for concrete examples.
You clarified one thing for me,
"The actual limitation is the internal binary precision, not a decimal precision.
Second, the formatting limit is 15 significant digits, not decimal places.
For example, 12345.6789012345 has only 10 decimal places, but it has 15 significant digits, just as 0.123456789012345 does."
That is the point where my calculations go wrong, but since I added a check, which notify’ s me if there is a difference in the total horizontal compared to the total vertical. It makes it easier for me to rule out any problems.
I have added the example you requested. Hope this clarifies it for you.
Ignore my previous response to your latest follow-up comments. I am rushed, and my response was ill-considered.
I will reply later -- unless by marking your follow-up comments as "best", your intention is to end the discussion.
- Hi Joe,
Don't know how to attach the excel file. But your explanation of digits and decimale is sufficient to me. I only wish I could sent the file but you can close the case. Thanks for your reply kind regards eddyEExmannhotmailcom wrote: "your explanation of digits and decimale is sufficient to me. [.... So] you can close the case"
But you have some much bigger mathematical problems and some dubious decimal presentation issues that have nothing to do with the binary arithmetic issue that Hans and I addressed.
(The following assumes that the upper-left corner of the first table is A1.)
-----
1. Your calculation of "total" m3/hour is incorrect. Mathematically, it should be the weighted average of the daily m3/hour amounts, not merely their sum. Ostensibly:
=SUMPRODUCT(C12:O12, C11:O11) / SUM(C11:O11)
Alternatively:
=SUM(C10:O10) / SUM(C11:O11)
The two formulas return the same result (subject to binary arithmetic anomalies), namely 0.304761904761905, if your actual daily m3/hour values in C12:O12 are their exact daily ratios, for example G10/G11.
(The SUMPRODUCT result is slightly different with the rounded values that you posted.)
-----
2. Apparently, the "daily" totals in R3:R9 and the weekly totals in C10:O10 are based on actual values with more decimal precision than the values that you posted in C3:O9.
For example, based on the posted values in C3:O9, =SUM(C3:O3) is 4.40 instead of 4.44 that you posted in R3. And =SUM(I3:I9) is 10.00 instead of 10.10 that you posted in I10.
Consequently, we cannot duplicate the visable difference (0.000000000000007) between your total in R10, presumably =SUM(C10:O10), and the sum of the "daily" totals, presumably =SUM(R3:R9).
(In the future, please post your formulas as well as their results, at least.)
Nevertheless, there is an invisible difference between those sums based on the posted values.
Both sums appear to be 50.9000000000000. But with formulas like =SUM(C3:O3) in V3:V9 and =SUM(C3:C9) in C14:O14, the results of =SUM(V3:V9) in V10 and =SUM(C14:O14) in P14 actually differ by about 1.42E-14 (**).
The reason is not so much because the order of operations is different, but because the values are different.
Nevertheless, for either reason, the explanation is the same: the internal binary representation of most decimal fractions is not exact, and the binary approximation can vary depending on the magnitude of the value. Consequently, the binary arithmetic result can differ infinitesimally from the expected decimal result.
For example, IF(10.1 - 10 = 0.1, TRUE) returns FALSE(!) because the binary approximation of 0.1 in 10.1 differs from the binary approximation of 0.1 by itself.
In P14, the sum is 11.00 + 7.60 + 10.00 + 6.00 + 16.30. The 17-digit (*) approximation is 50.900000000000006.
In V10, the sum is 4.40 + 7.40 + 6.40 + 8.60 + 7.40 + 7.90 + 8.80. The 17-digit approximation is 50.899999999999991.
Aside.... The following is an example where the order of operations makes a difference (compare with P14): 11.00 + 10.00 + 6.00 + 16.30 + 7.60 results in the 17-digit approximation 50.899999999999999.
(By coincidence, that is also the binary approximation of the constant 50.9.)
-----
TMI....
(*) Excel does not format the 17-digit approximation except in XML files, like the internal represenation of xlsx and xlsm files, and when transferring values between some applications, like MS Access. 17 significant digits is necessary and sufficient to replicate the internal binary representation with no loss of binary precision.
(**) The difference of 1.42E-14 is invisible if we calculate the formula =P14-V10 (might return exactly zero artificially) or the conditional expression P14=V10 (returns TRUE). That is due to dubious design "features" that are unique to Excel.
In contrast, =(P14-V10) with redundant parentheses does display 1.42E-14 when formatted as General or Scientific. And both ISNUMBER(MATCH(P14, V10, 0)) and P14 - V10 = 0 return FALSE because of the actual infinitesimal difference.
