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