Sum of numbers + 0

问题:

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

回答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.

回答2:

as I think, might be some different formula on A1 cell is already placed or the type of A1 Cell is int and Value becomes in decimal or else, so make try this formula in different cell.

回答3:

I wonder why you are using =SUM(C3:C3) etc. That is, Why use the sum function on a single cell. Unless there is a specific reason, I suggest you use = C3 instead. I've found that using the summation on a single cell can sometimes return answers that are incorrect. Don't know why, but it does. Therefore, long ago I quit using the sum unless there is a range that includes more than one cell.