Rounding differences in formula vs. hand-entered

One is 0.899999999999991 vs. 0.900000000000006

I just opened your file in Excel 2010 and it behaves just as you describe for 2013 for 1 and 2 decimal places. The values however are identical to those displayed by JieJenn when taken to many more decimal places.

Your computer, and therefore Excel, use a binary floating point format to store numbers. That format cannot store most decimals exactly, in the same sense that a finite base-10 decimal can't be exactly equal to 1/3. When you add or subtract inexact numbers, you frequently get even less exact numbers.

The manually-entered 99.1 value in F9 is stored as 4058C66666666666 (the hex representation of the 64-bit double-precision number). The SUM formula in F3 returns the result 4058C66666666667; it differs in the least significant bit.

Originally Posted by shg

Your computer, and therefore Excel, use a binary floating point format to store numbers. That format cannot store most decimals exactly, in the same sense that a finite base-10 decimal can't be exactly equal to 1/3. When you add or subtract inexact numbers, you frequently get even less exact numbers.

These numbers are pretty far from being inexact; they're all rational numbers. There's no "1/3" being forced to decimal, for example.

Originally Posted by shg

The manually-entered 99.1 value in F9 is stored as 4058C66666666666 (the hex representation of the 64-bit double-precision number). The SUM formula in F3 returns the result 4058C66666666667; it differs in the least significant bit.

If I understand you correctly, you're saying that a result of 0.9 is stored differently if it's the result of a calculation versus a hand-entered number?

The ubiquitous floating point.

As dflak suggests I put

Formula:

=ROUND(100-F2,1)

in J2:J3 and results in K2 and K8 are now identical.

Originally Posted by Gunther Maplethorpe

So, why are ANY of the cells going out to the umpteenth decimal? None of the original cells go out that far; none of them have any precision beyond the first decimal.

Part of the confusion arises because Excel formats only up to 15 significant digits, even though the exact internal representation might be more precise.

(Note: It does not limit internal representation or arithmetic to 15 significant digits. That is a common misunderstanding that finds its way into most explanations, even Microsoft's explanation in KB 78113 (click here) at http://support.microsoft.com/kb/78113. However, Excel does limit data entry to the first 15 significant digits, replacing digits to the right with zero.)

So both F3 and F9 appear to be exactly the same, even when formatted to 13 or more decimal places (at least 15 significant digits). But in fact, they are different:
F3: 99.1000000000000,085265128291212022304534912109375
F9: 99.0999999999999,94315658113919198513031005859375

(I use comma to demarcate the first 15 significant digits.)

The reason for the infinitesimal difference is: the constants in C3:D3 and C9:D9, which are the same, are not exactly as they appear, again due to the Excel formatting limitation. They are:
1.2: 1.19999999999999,99555910790149937383830547332763671875
66.9: 66.9000000000000,05684341886080801486968994140625

The 64-bit binary floating-point representation of these decimal constants is inexact because numbers are approximated by the sum of 53 consecutive powers of 2 ("bits") multiplied by an exponential factor.

For example, with pencil and paper, try to calculate 0.1 exactly as the sum of 1/16 + 1/32 + 0/64 + .... It cannot be done, even with an "infinite" number of consecutive powers of 2, much less with a limited number.

Note that F3 is infinitesimally larger than F9. So 100-F3 in J3 is infinitesimally smaller than 100-F9 in J9; and AVERAGE(1,J3) in K2 is infinitesimally smaller than AVERAGE(1,J9) in K8. [1]
J3: 0.899999999999991,4734871708787977695465087890625
J9: 0.900000000000005,684341886080801486968994140625
K2: 0.949999999999995,73674358543939888477325439453125
K8: 0.950000000000002,8421709430404007434844970703125

K2 and K8 would appear to be the same if you had formatted to 2 decimal places, which rounds the appearance (not the actual value, in this case). But they appear to be significantly different because you format (round the appearance) to 1 decimal place.

(If you try to duplicate the arithmetic with pencil and paper and the internal representations above, the results might not be exactly the same. This is because Intel-compatible CPUs actually use an 80-bit binary floating-point representation when performing calculations. But the same principles apply.)


-----
[1] Although the following is correct, it is not needed to understand the explanation above. So I moved it into this footnote.

Sometimes, insignificant (invisible) infinitesimal differences make a signficant (visible) difference, again because of the limited number (53) of consecutive powers of 2. For example, that explains why IF(100.1 - 100 = 0.1, TRUE) returns FALSE(!).

It takes 6 bits to represent 100; that is, integers in the range of 64 to 127. Ostensibly, that leaves fewer bits (47 = 53 - 6) to represent the decimal fraction; so there are fewer consecutive powers of 2 to sum. That changes the approximation of 1/10 in 100.1.

These numbers are pretty far from being inexact; they're all rational numbers. There's no "1/3" trying to forced to decimal, for example.

You are correct in saying that they are "rational numbers" (numbers that can be expressed as the ratio of two integers), but not all rational numbers can be represented as terminating decimals in decimal notation, and even fewer rational numbers are terminating decimals in binary notation (where computer's do their computations). For example, the 1/10=0.1 in decimal = 0.000110001100011... in binary. Simply having the 1/10th fraction in the problem introduces rounding error. Only powers of 2 (1/2, 1/4, 1/8, 1/16, etc) will be terminating decimals in binary space, so any fraction beyond those will introduce rounding error.

If I understand you correctly, you're saying that a result of 0.9 is stored differently if it's the result of a calculation versus a hand-entered number?

Perhaps not everytime, but in many cases, yes. 0.9 is stored differently depending on the calculation path used to get there.

In our hypothetical four digit decimal computer, there are several different ways to get 2/3. Hand entered it might be 0.6667. We might calculate 1/3+1/3=0.3333+0.3333=0.6666. Or we might calculate 1-1/3=1-0.3333=0.6667. 3 different approaches and 2 different answers. This is the kind of thing that is going on with just about every computation you perform in Excel (or other computer programming language).