excel - Sum of numbers + 0

Question

I am having a problem with a small number. I am using SUM function to sum certain numbers. If I add a zero to the range, it is not displaying zero. I don't know why.

A1

=SUM(B1:R1)*-1

C1 to L1

266864  -100000 -15136.15   -23688.82   -120870 -7169   -5550   1224    -0.03   4326

A2

=SUM(B2:R2)*-1

C2 to M2

=SUM(C3:C3) =SUM(D3:D3) =SUM(E3:E3) =SUM(F3:F3) =SUM(G3:G3) =SUM(H3:H3) =SUM(I3:I3) =SUM(J3:J3) =SUM(K3:K3) =SUM(L3:L3) =SUM(M3:M3)

A3

=SUM(B3:R3)*-1

C3 to M3

266864  -100000 -15136.15   -23688.82   -120870 -7169   -5550   1224    -0.03   4326    0

A1 is displaying 0, but A2 and A3 displays 9.09E-13

Answer 1

The number 9.09E-13 is another way (scientific notation) of saying 0.000000000000909495, a very small decimal number. What you are experiencing is a 15 digit precision floating point error.

????

Typically, you would use the ROUND function or something similar to remove the error (if you have to).

See Floating-point arithmetic may give inaccurate results in Excel for more information.