Sum not equal to zero

Question

The sum is not equal to zero

If using round to make sure it only have two decimal point, when sum it up it still can't get zero.

i.e. (A)-(B)=(C), (C)-(A)=(D), and (B) should equal to (D), if using excel, it won't get the same figure, just because of decimal.

I have checked with my colleague, whoever update their computer, will occur the problem as mentioned above (In my office, some of the computer did not update will not have this issue).

Answer 1

Hi EricLim8

Greetings! I am Vijay, an Independent Advisor. I am here to work with you on this problem.

You are witnessing Floating Point Arithmetic at work.

https://www.microsoft.com/en-us/microsoft-365/b...

http://support.microsoft.com/kb/78113/en-us

Hence, you need to use following kind of formula

=ROUND(Your Formula,2)

Note: You can't predict where Floating Point stuff will hit you.

Do let me know if you have any more question or require further help.

Answer 2

Eric wrote:

If using round to make sure it only have two decimal point, when sum it up it still can't get zero.

You are rounding the wrong things.  In the second example, assuming that column B contains constants (e.g. you typed 2942.7; you did not calculate it), there is no reason to round them to the same or greater precision.

What you need to round is the SUM formula.  Write =ROUND(SUM(B2:B180),2).

In general, whenever you expect a calculation to be accurate to some number of decimal places (e.g. 2), explicitly round the calculation to that number of decimal places (e.g. ROUND(...,2)) -- and not to an arbitrary number of decimal places, like 10, as some people suggest.

This has nothing to do with the version or update of Excel that you have.  It is an inherent side-effect of the way that Excel represents numeric values internally, namely 64-bit binary floating-point.  Most decimal fractions cannot be represented exactly in that binary form.  And the binary approximation of a particular decimal fraction varies depending on the magnitude of the number.

That is why, for example, IF(10.1 - 10 = 0.1, TRUE) returns FALSE(!).

However, there is an alternative to explicit rounding formulas:  setting the "Precision As Displayed" option.  That might explain why other users do not experience the problem.  Or they might simply have not stumbled across the problem yet.

I deprecate the use of PAD for many reasons.  Chief among them:  merely setting the PAD can alter some constants irreversibly, which can alter the outcome of calculations anywhere in the workbook.

For that reason, if you choose to experiment with PAD, be sure to make a back-up copy of the Excel first.