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 (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.