I agree with your diagnosis, required—it has to be a rounding issue—and with gebobs' suggestion. But I'll expand on it a bit in case there's still confusion: As a matter of picky terminology, I would say that the value in C9
is accurate, just not precise.
Take an example:
Code:
3.14159265359
2.71828182846
2.99792458000
1.41421356237
Their precise sum is 10.27201262442, but unless you tell Excel to use special formatting all it'll show you is 10.27201262. That answer is accurate, it's just not precise to all 13 significant digits.
Now tell Excel to display them to just
one decimal point, like this:
If these were the numbers' true values, the total should be 10.2, but in fact Excel claims their sum is 10.3—and Excel is correct. When you display them using just one significant digit, the
display says 3.1 but the value behind the scenes, the one that's being summed, is still 3.14159-etc.
What do you do about it? Well, it depends on your needs. It would probably be more accurate for many purposes to leave it as it is, giving 10.3 as the sum rather than 10.2. But if you need to for some reason, you can round the individual numbers to their truncated form, and the sum of
those values (rather than the real ones) will come out to 10.2 as you expected.
Extra credit for identifying the sample numbers above.
