Forum Discussion
Calculation error
Hello,
I have a matrix of numeric values 13 by 7 cells.
If I summarize the horizontal figures (13 columns in total) it gives me a total.
If I do the same (different purpose) for the vertical (7 rows in total) it also gives me a total.
No formatting. except 2 digits behind comma.
I thought the total of the 13 columns would be the same as the total of the 7 rows.
I learned that Excel calculates through 15 digits behind the comma.
But I find that the total of the 7 rows it calculates to 14 digits behind the comma, the 15th digit is forgotten(?). The total of the 13 columns also loses its 15th digit behind the comma.
That would make is even, but no horizontal total of columns is NOT EQUAL TO vertical total of rows.
Please explain or help to get it accurate.
On request, I can supply the matrix to describe my calculation error further.
Kind regards Eddy
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.
Because of the way Excel stores and processes numbers, the order of calculations can cause tiny differences.
To work around this, use the ROUND function to round the totals to a 'reasonable' number of decimal places.
For example, if the numbers in the matrix all have up to 2 decimal places, use
=ROUND(SUM(...), 2)
That way, the row total and column total will be equal.
HI Hans, yes that is possible, but off the approx 2366 figures only 2 batches matrix of 182 figures total (2 times 13columns-7rows) don't add up. Don't want to round, because with such large amount of figures (still growing) I would filter the abnormale figures.
If the numbers in the matrix have at most n decimal places, their sum by row or column will also have at most n decimal places, so it makes sense to round to n decimal places. If you want, you can round to n+1 or n+2 decimal places to find discrepancies. But you cannot expect calculations to be correct to 15 decimal places.
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 eddy
Totalen Gas Verbruik m3 Datum van Temp.- / Energie 13 14 15 16 17 18 19 20 21 22 23 24 25 Temp.- / Energie Dag Totaal Totaal Werkweek Totaal Weekend 15-4-2024 MA 0,0 0,0 0,0 0,0 1,3 2,0 0,4 0,3 0,4 0,0 0,0 0,0 0,0 MA 4,44 16-4-2024 DI 0,0 0,0 0,0 0,0 1,8 2,3 0,1 0,5 2,7 0,0 0,0 0,0 0,0 DI 7,40 17-4-2024 WO 0,0 0,0 0,0 0,0 1,4 1,6 1,0 0,6 1,8 0,0 0,0 0,0 0,0 WO 6,50 18-4-2024 DO 0,0 0,0 0,0 0,0 1,8 1,1 2,1 0,8 2,8 0,0 0,0 0,0 0,0 DO 8,56 19-4-2024 VR 0,0 0,0 0,0 0,0 1,7 0,6 1,8 0,5 2,8 0,0 0,0 0,0 0,0 VR 7,43 34,33 20-4-2024 ZA 0,0 0,0 0,0 0,0 1,5 0,0 2,1 1,5 2,8 0,0 0,0 0,0 0,0 ZA 7,91 21-4-2024 ZO 0,0 0,0 0,0 0,0 1,5 0,0 2,5 1,8 3,0 0,0 0,0 0,0 0,0 ZO 8,89 16,79 Datum t/m Totaal 0,0 0,0 0,0 0,0 11,1 7,6 10,1 6,1 16,3 0,0 0,0 0,0 0,0 Totaal 51,12 0,000000000000007 Uren 0,0 0,0 0,0 0,0 56,0 28,2 29,5 17,0 37,3 0,0 0,0 0,0 0,0 Uren m3/uur 0,00 0,00 0,00 0,00 0,20 0,27 0,34 0,36 0,44 0,00 0,00 0,00 0,00 m3/uur 1,60 Totalen Gas Verbruik m3 Datum van Temp.- / Energie 13 14 15 16 17 18 19 20 21 22 23 24 25 Temp.- / Energie Dag Totaal Totaal Werkweek Totaal Weekend 20-5-2024 MA 0,0 0,0 0,0 0,0 0,5 0,7 0,0 0,1 0,0 0,0 0,0 0,0 0,0 MA 1,32 21-5-2024 DI 0,0 0,0 0,0 0,0 0,4 0,3 0,0 0,0 0,0 0,0 0,0 0,0 0,0 DI 0,76 22-5-2024 WO 0,0 0,0 0,0 0,0 0,0 0,6 0,0 0,0 0,0 0,0 0,0 0,0 0,0 WO 0,70 23-5-2024 DO 0,0 0,0 0,0 0,0 0,4 0,4 0,0 0,0 0,0 0,0 0,0 0,0 0,0 DO 0,89 24-5-2024 VR 0,0 0,0 0,0 0,0 0,0 0,5 0,0 0,0 0,0 0,0 0,0 0,0 0,0 VR 0,50 4,16 25-5-2024 ZA 0,0 0,0 0,0 0,0 0,5 0,2 0,0 0,0 0,0 0,0 0,0 0,0 0,0 ZA 0,76 26-5-2024 ZO 0,0 0,0 0,0 0,0 0,4 0,1 0,2 0,0 0,0 0,0 0,0 0,0 0,0 ZO 0,71 1,47 Datum t/m Totaal 0,0 0,0 0,0 0,0 2,4 3,0 0,2 0,1 0,0 0,0 0,0 0,0 0,0 Totaal 5,63 -0,000000000000001 Uren 0,0 0,0 0,0 0,0 46,8 116,2 2,5 2,5 0,0 0,0 0,0 0,0 0,0 Uren m3/uur 0,00 0,00 0,00 0,00 0,05 0,03 0,09 0,04 0,00 0,00 0,00 0,00 0,00 m3/uur 0,20
