EXCEL SUM PROBLEM ?

Question

TEST-1:

1749.1

166.0

164.7

171.3

167.1

165.2

154.3

152.7

151.2

116.7

-158.3

-1999

The answer of sum is 1001**(it's correct.)**

But when I change the number -1999 to -2000,

TEST-2:

1749.1

166.0

164.7

171.3

167.1

165.2

154.3

152.7

151.2

116.7

-158.3

-2000

The answer of sum is 999.99999999999900000000 (It's notequal to1000 , Why?)

When I chang the sorts of these numbers,

TEST-3:

-2000

1749.1

166.0

164.7

171.3

167.1

165.2

154.3

152.7

151.2

116.7

-158.3

The answer of sum is 1000 (It's correct again.)

What's the problem in the TEST-2 case?

	TEST-1	TEST-2	TEST-3
1	1749.100000000000000	1749.100000000000000	-2000.000000000000000
2	166.000000000000000	166.000000000000000	1749.100000000000000
3	164.700000000000000	164.700000000000000	166.000000000000000
4	171.300000000000000	171.300000000000000	164.700000000000000
5	167.100000000000000	167.100000000000000	171.300000000000000
6	165.200000000000000	165.200000000000000	167.100000000000000
7	154.300000000000000	154.300000000000000	165.200000000000000
8	152.700000000000000	152.700000000000000	154.300000000000000
9	151.200000000000000	151.200000000000000	152.700000000000000
10	116.700000000000000	116.700000000000000	151.200000000000000
11	-158.300000000000000	-158.300000000000000	116.700000000000000
12	-1999.000000000000000	-2000.000000000000000	-158.300000000000000
sum	1001.000000000000000	999.999999999999000	1000.000000000000000
	OK	???	OK

Answer 1

HansV wrote:

[Chang wrote:]

And we must fix the errors on a case by case, don't we?

Yes...

It is correct that the infinitesimal differences between binary computer and decimal manual arithmetic are unpredictable.

Therefore, IMHO, the better advice is:  whenever we expect a calculation to be accurate to "n" decimal places, we should explicitly round to that number of decimal places.  (Not to an arbitrary number of decimal places like 10, as some people suggest.)

Formatting alone only affects how a value appears.  It does not change the actual value [1].

In other words, your formula should always be =ROUND(SUM(B2:B13),1), not just when you discover a "mistake".  If you do not always do that because the sum looks correct with one set of data, changing the data later might alter the correct appearance and use of the sum.

---

FYI, in test #1, the sum is not exactly 1001, assuming that the data in B2:B13 are constants.

That is just an illusion caused by the fact that Excel formats only the first 15 significant digits (rounded), and because the equal operator ("=") rounds the left and right operands to 15 significant digits just for the comparison.

In fact, the sum is about 9.09E-13 less than 1001.  That can affect the outcome of some functions.  For example, with the sum in B14, =LOOKUP(B14,{1000,1001}) returns 1000 (!), not 1001.

(LOOKUP matches the largest value less than or equal to the lookup value, not exactly equal to the lookup value.)

In test #3, moving -2000 to the front does indeed cause the final sum to be exactly 1000.  But the "correction" is an accident of implementation.

For example, with constants in D2:D13, =SUM(D2:D3) (-2000+1749.1) might appear to be exactly -250.9, but in fact, it is about 8.53E-14 less.  So, =LOOKUP(SUM(D2:D3),{-251,-250.9,-250.8}) returns -251 (!), not -250.9.

---

[1] Unless we set the "Precision as displayed" option and choose a format other than General.  I do not recommend setting PAD for a number of reasons.  Chief among them is:  setting PAD might reduce the precision of constants permanently.  For that reason, if you "must" experiment with PAD, be sure to make a copy of the Excel file before setting PAD, so you can revert to the original data when you discover undesirable side-effects of setting PAD.

Answer 2

These are rounding errors caused by the conversion from decimal numbers to binary numbers and back.

To get around this, use the ROUND function, for example:

=ROUND(SUM(B2:B13),1)

Answer 3

Thanks a lot for replying.

So these rounding errors may happen randomly.

And we must fix the errors on a case by case, don't we?

Best regards,

W.C. Chang

Answer 4

I don't know which method Excel uses internally to compute a sum...

Answer 5

Thanks a lot for helping.

But I don't understand why it doesn't happen in TEST-3.

How do I know when it will happen?

Best regards,

W.C. Chang