-13601.145900
3400.286475
2888.237987
3400.286475
3400.286475
512.048488
If I use SUM() to add them up, they should add to zero, however Excel shows that they add to: 1.47792889038101E-12
I know Excel has a limit of 15 digits of accuracy, however none of these numbers exceeds that limit (the longest is 10, including decimal places). Would like to know why this is happening, and if there's any way to have this add to 0 without rounding the numbers.
My sum cell shows 0 also, but if I select the cell, press F2, and then press F9, it shows that the cell evaluates to 1.47792889038101E-12. I want it to evaluate to 0.
Last things first: Whenever you expect results to be accurate to a specific number of decimal places, you should explicitly round. In your case:
ROUND(SUM(A1:A6),6).
Re: "I know Excel has a limit of 15 digits of accuracy". That is an oft-repeated misstatement. As you note, if that were the case, there would be no accuracy problem.
The correct statements are:
1. On data entry, Excel does truncate (not round!) numbers to 15 significant digits.
2. Excel displays (formats) only up to 15 significant digits, rounding the 16th (usually; there is an anomaly, which might be defect).
3. Internally, numbers are represented using 64-bit
binary floating-point; specifically, the sum of 53 consecutive powers of 2 ("bits") times an exponential factor. Consequently, most
decimal fractions cannot be represented exactly.
Try it yourself! Try to represent 0.1 (1/10) as a sum starting with 1/(2^4) (1/16, because 1/4 > 1/10) and decreasing powers 2, some of them with a zero numerator. It cannot be done, even with in "infinite" series of powers of 2, much less just 53.
When arithmetic is performed with these inexact values, sometimes the infinitesitmal differences compound; sometimes they cancel out. Sometimes order matters. For example, summing a lot of very small numbers, then adding that result to a relatively large number might have a different result than if we start with the very larger number.
Before we look at your complicated example, consider the following simple one:
IF(10.1 - 10 = 0.1, TRUE) returns FALSE(!).
The exact internal representations are (using comma to demarcate 15 significant digits):
10.1 = 10.0999999999999,996447286321199499070644378662109375
10.1 - 10 = 0.0999999999999996,447286321199499070644378662109375
0.1 = 0.100000000000000,0055511151231257827021181583404541015625
(Aside: That demonstrates that "Excel" -- really 64-bit binary floating-point -- is not limited to 15 significant digits of accuracy. But it is true that we can use 17 significant digits, again not 15, to reproduce any internal representation exactly. However, that is not to say that 17-sig-digit number is the exact internal representation. Usually, it is not.)
Given the internal representation of 10.1, the internal representation of 10.1 - 10 should not be surprising.
The important point is: the representation of the constant 0.1 is different than the representation of the 0.1 part of 10.1.
The reason, again, is: we have only 53 "bits" (times an exponential factor) to represent numbers. In the case of 10.1, 4 bits are needed to represent 10. That leaves only 49 bits (consecutive powers of 2) to represent the 0.1 part. In contrast, we have 53 bits to represent the constant 0.1.
(That would not matter if the last 4 bits of the constant 0.1 were zero. But they are not.)
In your example, the exact internal representations are:
-13601.1458999999,9952851794660091400146484375
3400.28647499999,98821294866502285003662109375
2888.23798699999,997552367858588695526123046875
3400.28647499999,98821294866502285003662109375
3400.28647499999,98821294866502285003662109375
512.048488000000,0202926457859575748443603515625
If you're adventurous, try adding those numbers with pencil and paper (and probably a good eraser :->). I think you'll see the result is close to 0.00000000000147792889038100,83866119384765625
which is the 1.48E-12 result that you see.
BTW, the pencil-and-paper result might not be exactly the same. The reason is two-fold: (1) Intel-compatible CPUs actually use an internal 80-bit binary floating-point representation for doing arithmetic; but (2) Excel rounds the 80-bit intermeditate result of each pairwise operation to the 64-bit representation.
Point #2 is one reason why Excel and VBA results sometimes differ infinitesimally. VBA tries to use the 80-bit intermediate results.
Finally, in Excel, there are times with =(A1=A2) is TRUE and =A1-A2 returns exactly zero, but A1-A2=0 is FALSE and A1-A2-0 is not exactly zero.
That is because of a dubious heuristic whereby Excel sometimes considers differences that are "close to zero" to be exactly zero. But as demonstrated, the heuristic is implemented inconsistently. Consequently, it compounds the mystique of 64-bit binary floating-point arithmetic.
