Which rounding method ?

[Jean-Claude Arbaut]

I tried a simple "epsmach" test:

+-----------+-----------+-----------+-----------+
| | A | B | C |
+-----------+-----------+-----------+-----------+
| 1 | 1 | =A1+1 | =B1-1 |
+-----------+-----------+-----------+-----------+
| 2 | =A1/2 | =A2+1 | =B2-1 |
+-----------+-----------+-----------+-----------+
| 3 | =A2/2 | =A3+1 | =B3-1 |
+-----------+-----------+-----------+-----------+
| 4 | =A3/2 | =A4+1 | =B4-1 |
+-----------+-----------+-----------+-----------+
... ... ... ...

With this I can compute the smallest x such
that 1+x > 1, in computer arithmetic. I would
expect to find the following

+-----------+-----------+-----------+-----------+
| 53 | 2.220e-16 | 1.0000... | 2.220e-16 |
+-----------+-----------+-----------+-----------+
| 54 | 1.110e-16 | 1 | 0 |
+-----------+-----------+-----------+-----------+

This is the expected result with IEEE754 arithmetic,
which is the one found on PowerPC and Pentium processors.

Instead, Excel gives:

+-----------+-----------+-----------+-----------+
| 51 | 8.882E-16 | 1.0000... | 8.882E-16 |
+-----------+-----------+-----------+-----------+
| 52 | 4.441E-16 | 1 | 0 |
+-----------+-----------+-----------+-----------+

Does Excel use a special rounding method ?
Or perhaps software floating point ?

 

[JE McGimpsey]

Does Excel use a special rounding method ?
Or perhaps software floating point ?

Excel uses IEE754, for the most part. See

http://support.microsoft.com/default.aspx?scid=kb;en-us;78113

Note that order of operation matters - if you use

B1: =A1-1
C1: =B1+1

you'll get an extra non-zero difference.

However, both examples are well within XL's published specifications
(Help: Microsoft Excel 2004 specifications and limitations):

Number precision 15 digits

For an interesting discussion, see the "Errors Designed Not To Be Found"
section of

http://www.cs.berkeley.edu/~wkahan/Mindless.pdf

 

[Jean-Claude Arbaut]

Your links are very interesting, thank you !

But now I think... my question was stupid: in column A computations
are exact (division by 2), and column B is exact whenever the
result of the addition can be represented in IEEE 754 double format,
and 1+2^(-52) is representable in double precision. There is no
rounding here, at least if processor arithmetic is used (you can
check this with gcc or awk). So Excel doesn't do really what
is expected by IEEE 754: when the exact result of an operation can
be represented, it *must* be returned by the operation.
You can have a look at the standard: http://www.validlab.com/754R/
and http://www.validlab.com/754R/standards/754.pdf. Rounding occurs
only if the exact result is not representable.


----------------------------------------------------------------
Another strange point: with my little test, Excel can represent
1+2^(-50) = 1.000000000000000888178419700..., so there are more
than 16 decimal digits. If you try to enter by hand the exact
values (computed with bc):
1.00000000000000088817841970012523233890533447265625 yields 1
1.0000000000000099999999999999999999999 yields 1
1.00000000000001 yields x > 1
So Excel seems to truncate numbers to 15 digits when entered litteraly,
and can compute with more than 16. It isn't really a problem, but it
shows that the "strtod" function used by Excel is not very accurate.

If you try the same with awk:
awk 'BEGIN{
a=1.00000000000000011102230246251565404236316680908203125;
printf("%.24g\n",a)
}'

awk 'BEGIN{
a=1.00000000000000011102230246251565404236316680908203126;
printf("%.24g\n",a)
}'

The first prints 1 and the second prints 1.0000000000000002220446,
as expected.


----------------------------------------------------------------
Now, let's have a look at the Excel file, just to be sure stored
results are the same as printed results... So store the little
epsmach test in my previous post, and "hexdump" the file.
you will find "00 00 00 00 00 00 d0 3c",
"04 00 00 00 00 00 f0 3f" and "00 00 00 00 00 00 d0 3c",
which represent A51 = 2^(-50), B51 = 1+2^(-50), and C51 = 2^(-50).
But you will also find "00 00 00 00 00 00 b0 3c", "01 00 00 00 00 00 f0 3f",
and "00 00 00 00 00 00 00 00", which represent
A53 = 2^(-52), B53 = 1+2^(-52), and C53=0. Actually the addition
was correct, but the subtraction is wrong !!! Very strange, isn't it ?
And there is more: fill column D with =SI(C1=0,"zero","non zero"), and
column E with =SI(B1=$A$1,"one","not one"). Hum, you will probably type
IF instead of SI, my Excel is french, you know... Well the result is
amazing: column D prints "non zero" in rows 1 to 51, and "zero" in rows > 51
which is expected, since C51 = 0 (even if this is not what I expect by
IEEE 754...). Column E prints "not one" in rows 1 to 48 and "one" in rows >
48.
So, 1+2^(-48) is a number x such that x-1 is not zero (even in Excel!), but
x = 1 according to Excel. If you print more digits in column B, you will
see numbers are truncated to 15 significant decimal digits, for example
B48 is printed 1.00000000000001000000000000000 and B49 is printed
1.00000000000000000000000000000.
Well, this result is not too amazing if we remember Excel on promises *15
digits*,
and B49 = 1.000000000000003552713678800500929355621337890625. So if B49 is
truncated,
is shows 1, necessarily. BUT I find very amazing that Excel uses different
techniques
with different operation. Remember: addition in line 53 is exact, but
subtraction
is not. And equality test is truncated to 15 digits (or seems to be). This
can be
very annoying in computation where one expects full IEEE compliance.
Obviously Excel
is not compliant.


----------------------------------------------------------------
Just another equality test:
A1: 1
A2: 2
A3: 3
A4: =A1/A3
A5: =A1-A4 // A5 = 2/3
A6: =A2/A3 // A6 = 2/3
A7: =A5-A6
A8: =A2*A5-A1
A9: =A2*A6-A1
A10: =A8-A9
A11: =A5=A6
A12: =A8=A9

Now the results:
A7 is zero, and A11 is TRUE. Well... why not ? The computations could have
given exactly
the same number.
A10 is NOT zero but A12 is TRUE !!!!!! Ok, so for Excel, when difference is
not zero,
numer can be equals... Funny

I know, it still follows Excel contract: 15 digits... But, it's quite
disturbing.



Le 13/06/2005 05:30, dans
(e-mail address removed), « JE McGimpsey »