SUM: Ghost Numbers?

Question

Can someone explain why H1 isn't 0?

	A	B	C	D	E	F	G	H
1	130.6	27.85	17.7	42.38	42.67		130.6	2.84217E-14

A-F:  Data entered as shown

G: =SUM(B1:F1)

H: =G1-A1

Answer 1

Actually, it is also prudent to do:

=ROUND(SUM(B1:F1), 2)

In general, whenever you expect a calculation with decimal fractions to be accurate to some precision, you should explicitly round to that precision (2 decimal places, in this case).

---

In fact, if we enter the constants that you posted into A1:E1, =G1-A1 is exactly zero (0.00E+00) when G1 is simply =SUM(B1:F1).

But =G1-A1-0 is indeed 2.84E-14.

So the exact zero result of =G1-A1 is due to an Excel trick ("close to zero" heuristic; a misnomer) whereby Excel replaces the exact arithmetic result with zero.  The redundant -0 disables that dubious and inconsistent feature.

So presumaby in your Excel file, one or more the values in A1:E1 is the result of a calculation that does not have the same binary representation as the constant.

You should consider explicitly rounding those calculations, as well.

Alternatively, the actual formulas in the your Excel file are different (unlikely!).

Answer 2

This is due to tiny, unavoidable rounding errors.

Since the numbers in A1:G1 have at most 2 decimal places, use

=ROUND(G1-A1,2)

in H1.

Answer 3

Your summary is unnecessary and, more importantly, it is incomplete.

Since we expect G1-A1 to be accurate to 2 decimal places (because some of the data has 2 dp), it is prudent to also write ROUND(G1-A1,2), as Hans suggested.

You might say that it is unnecessary in this instance if we do ROUND(SUM(B1:F1),2), as I suggested.

But the reverse is also true:  ROUND(SUM(B1:F1),2) is unnecessary if we do ROUND(G1-A1,2), as Hans suggest.

The important take-away is, as I wrote:  *whenever* you expect a calculation with decimal fractions to be accurate to some precision, you should explicitly round.

Please stick to "moderating", and leave the answers to those who have knowledge to share.

Answer 4

Hi,

Here is the summary for the case.

Subject:

================

rounding errors

Symptom:

================

incorrect result of formula

Solution:

================

Use Round function.

Example:

G1 =SUM(B1:F1) result as 130.6

A1 = 130.6

H1 =G1-A1 will show 2.84217E-14

To correct it use round function like

=ROUND(SUM(B1:F1), 2)

Regards,

Eric