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.
I'd use scientific notation for such numbers.
Hello again Hans,
I've been thinking about your statement: "But you cannot expect calculations to be correct to 15 decimal places."; It gets me puzzled is Microsoft states that Excel calculates up to fifteen decimal places.
Then why can't I expect Excel to be accurate up to fifteen decimals.
And one other thing if I calculate the following matrix:
“1” + “2” + “3” + “4” horizontal = “10”;
“5” + “5” + “5” + “5” horizontal = “20”;
“6” + “6” + “6” + “6” horizontal = “24”;
“7” + “7” + “7” + “7” horizontal = “28”;
= ”19”, “20”, “21”, “22” vertical.
It should sum vertical 10, 20, 24, 28 and sum horizontal 19, 20, 21, 22, and it does in Excel.
And the total of the matrix should be eighty-two, and it is.
I have checked and yes, it calculates correct, even if voor instance I don't use “1” but “0,000000000000001” (eq fourteen decimal zeros) and that for all the numbers in the example.
However, if the numbers get more 'difficult' there Excel makes a mistake and rounds to the wrong amount. I could supply the two matrixes where it occurs. And like I said only two matrixes produce the wrong answer of the 189 matrixes with real numbers I have collected so far.
The question, can I trust Excel up to fifteen decimal places or not, they (Microsoft) say it works with fifteen decimal places.
Kind regards, Eddy