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.
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.
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 eddy
EExmannhotmailcom 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.
Hello Joe (User2004),
I finally got around looking at your comment about how I calculate the average usage in m3/hour.
First of all let me thank you very much for your input, it is making me very eager to learn more.
Let me introduce myself.
I’m working with Excel version 2406 on a Windows 10 system and use Excel.xlsm files since I’m considering a further ease of inputting data through a programmed user interface.
I’ve been working (systems programmer) with mainframes during my working time (30 years), I stopped working at the age of 50 and love to play with computers for fun.
The Excel issue:
I’ll explain how I retrieve and calculate the numbers.
I have a matrix representing every day of the year and each day I have divided into 10-minute intervals.
So for a week I have a matrix of 7 * 144 (= 1008 cells) (a year 365 or 366 times 144 = 52560 -52704 cells)
Why 10-minute intervals, because than I can easily enter the measurement, I read from the equipment without having to consider the time.
I regularly have time’s that I read the measurement from the equipment:
Weekdays 07:00; 09:00; 16:30; 17:00; 22:30; 23:50.
Weekend (Saturday – Sunday) 08:00; 11:00; 16:00; 17:00; 23:00; 23:50.
The time’s I read the measurement isn’t chosen randomly, it’s the time where a possible temperature change will occur, since it’s in sync with my Thermostat.
Along with the 10-minute intervals I have registered the temperature I have set in the thermostat, which controls my environment.
From these numbers I create a second matrix consisting of 7 * 13 cells, where 7 is the number of days in a week and 13 is the possible temperature that is set in the thermostat, ranging from 13 up to 25 Co.
Currently (summer period) my thermostat is set to a default where it makes shure nothing is getting frozen meaning the pipes and including me.
I retrieve the figures from the first matrix by selecting the proper date and temperature and combining them in a new figure in the second matrix. For example, the temperature may fluctuate from 15 – 17 – 20 - 18 – 20 -21 – 19 – 15 during the day; equivalent to: night, morning, getting out of bed, daytime, coming home, night time, getting ready for bed, night.
Then finally calculate the total energy consumption for each day, a total of the working days and a total for the weekend and last but not least a total per temperature for that week. And from these totals I calculate the average usage m3/hour.
Now you state that I should use a different approach for calculating the m3/hour. You suggest I should use a calculated weighted approach. But I would not know what I should weight it against. Because the m3/hour at 07:00 costs me the same as the m3/hour at 22:00 or at 03:00. So the figures from the first matrix are evenly important and are used to create the second matrix.
Whereas in the second matrix, if I consider the horizontal total per day; and try to match that to the vertical total per temperature. Overall the total of seven days and the total of 13 different temperatures should be the same.
BUT sometimes IS NOT.
For reading I normally format the figures to one or two decimals and understand that actually more decimals are used in the calculation.
This is where your input gave me some insight into digits and decimals, Although Microsoft says it will work with 15 decimals it can happen that more or less decimals are influencing the total that I want to calculate. In my case, where I have the current figures of 203 days in 144 intervals = 29.232 figures, Where only in two instances the total horizontal differs from the total vertical.
In the example you can find the differences in the week of april 15th to april 21st and may 20th to may 26th.
For you I have extracted portions of the excel sheet for you to look at.
I consider this case closed, thank you very much for your time and input.
If you would like I do have an other issue, where I move a formula to the right or left and the cell refences in the formula change correctly or only partly change correctly.
Kind regards, Eddy
I have a file ready to demonstrate the calculation error, don't know how to attach the file to this reply, please reply and I will send it directly. Kind regards, Eddy 'eexmann', 'hotmail', 'com'.Regarding your response: TL;DR. And since you repeatedly say "I consider this case closed", there really is no reason for me to help you further. But I cannot resist clarifying some things....
-----
EExmannhotmailcom wrote: "Now you state that I should use a different approach for calculating the m3/hour. You suggest I should use a calculated weighted approach. But I would not know what I should weight it against"
I showed you the formulas in my previous response.
The point is.... You calculate the m3/uur for each temperature (13 to 25) by calculating Totaal divided by Uren.
So likewise, we should calculate the overall m3/uur by calculating the sum of Totaal for all temperatures divided by the sum of Uren for all temperatures.
As I wrote previous: SUM(C10:O10) / SUM(C11:O11).
You should be able to see that that is not the same as the sum of the weekly m3/uur for all temperatures, which you calculate under Dag Totaal.
But the overall m3/uur, as I calculate it, is the same as the weighted average of the weekly m3/uur ratios.
As I wrote previously: SUMPRODUCT(C12:O12, C11:O11) / SUM(C11:O11).
The numerator is effectively C12:C11 + D12*D11 + ... + O12*O11, where C12 is m3/uur for temperature 13 and C11 is Uren for temperature 13.
Thus, the Uren for each temperature are the weighting factors.
Since m3/uur is calculated by Totaal / Uren for temperature 13, (Totaal / Uren) * Uren = Totaal (C10) for temperature 13. Thus, the SUMPRODUCT is the same as SUM(C10:O10).
So, the SUMPRODUCT/SUM formula is the same as the SUM/SUM formula.
-----
EExmannhotmailcom wrote: "For reading I normally format the figures to one or two decimals and understand that actually more decimals are used in the calculation."
But when you ask a question about the correctness of the calculation, obviously we need to know the actual values in the calculation, as well as the actual formulas that derived the values.
-----
EExmannhotmailcom wrote: "Although Microsoft says it will work with 15 decimals it can happen that more or less decimals [are involved]"
Yes. That is a constant source of confusion for many users. For some additional detail, but hopefully not TMI, see my first response to another recent question (click here).
-----
EExmannhotmailcom wrote: "I have extracted portions of the excel sheet for you to look at. I consider this case closed, thank you very much for your time and input. [....] I have a file ready to demonstrate the calculation error, don't know how to attach the file"
Since you "consider this case closed", I have no interest in the file.
But for your edification, you can attach files by clicking "browse files to attach" near the Post button at the bottom of the applet.
If you don't see that (I don't know why not; but other users say that, too), share a "view only" link to the file. Upload the file to a file-sharing website like box.net/files, dropbox.com, onedrive.live.com, etc. (But not Google Sheets.)
-----
EExmannhotmailcom wrote: "If you would like I do have an other issue"
What I "would like" has nothing to do with it.
But if you have another problem that you want help with, start a new thread for each separate problem. Someone might help you.
- Hi Joe and or Hans,
I greatly respect your effort. I will be abroad for a few days. But wil definitly come back to you next week after I have given your input some consideration. For now thank you very much. I appreciate it very much. Kind regards, Eddy
