What's wrong with this simple formula?

J

jaclh2o

I have this formula:
0.312600000000000000000000000-0.312400000000000000000000000
it should equal 0.000200000000000000000
but it equals 0.000199999999999978000000000
It would not be a problem except for a condition formula
Whats wrong?
 
H

Harald Staff

Hi

I hope you realise how small this error in reality is. See Chip's page
http://www.cpearson.com/excel/rounding.htm
on how rounding errors happens.
(Assuming your numbers are hardcoded numbers. If they are formula results,
these errors may pile up to finally make a difference.)

HTH. best wishes Harald
 
J

Jerry W. Lewis

Nothing is wrong. Per IEEE standard, this is exactly what should happen.

Help for "Excel specifications and limits" subtopic "Calculation
specifications" indicates that number precision is limited to 15 digits.
Actually, the issue is that Excel (and almost all other software
except for a few special purpose packages) does math in binary rather
than decimal. None of 0.3126, 0.3124, and 0.0002 can be exactly
represented in binary (they are repeating binary fractions, just as 1/3
is a repeating decimal fraction). The IEEE 754 standard defines the
number of bits to be used in approximating these inputs, and how the
repeating binary fractions should be rounded to achieve these
approximations; for details see
http://www.cpearson.com/excel/rounding.htm
http://support.microsoft.com/default.aspx?scid=KB;en-us;42980

Since the inputs are only approximate, it should be no surprise that the
output is approximate.

The binary approximation to 0.3126 is 1407825243516017/4503599627370496
which in decimal is
0.3125999999999999889865875957184471189975738525390625
Similarly the decimal representations of 0.3124 is
0.3124000000000000110134124042815528810024261474609375
Do the math and you will find that the exact difference of the
approximate inputs is
0.000199999999999977973175191436894237995147705078125
which Excel has correctly rounded to 15 digits, per their published
specification.

15 digits is listed as the precision limit because not all 16 digit
numbers can be achieved by rounding IEEE binary approximations. Rather
than trying to explain why you entered one 16 digit number and Excel
displayed a different 16 digit number, the developers decided never to
display more than 15 meaningful digits. If you ask for more than 15
digits, the trailing digits will arbitrarily be displayed as zeros.

You can use this 15 digit limit as a quick and dirty rule for predicting
the level of accuracy of calculations without exactly calculating binary
approximations. Your problem would then be expressed as
0.312600000000000????
-0.312400000000000????
----------------------
0.000200000000000????
which is consistent with Excel's reported result of
0.000199999999999978?

Jerry
 
J

Jerry W. Lewis

Jerry W. Lewis wrote:

....
The binary approximation to 0.3126 is 1407825243516017/4503599627370496
which in decimal is
0.3125999999999999889865875957184471189975738525390625
Similarly the decimal representations of 0.3124 is
0.3124000000000000110134124042815528810024261474609375
Click to expand...
....



The line begining "Similarly ..." should read

Similarly the decimal representation of the binary approximation to
0.3124 is

Jerry
 
H

Harald Staff

If I know him right, he doesn't use a computer for simple stuff like this
<g>

Best wishes Harald
 
M

Myrna Larson

If you meant me, I don't have either one. But I would be interested in seeing
your function just the same.
 
J

Jerry W. Lewis

My mistake. Here is the Maple code; suggestions appreciated. It is
pretty much hammer and tong. I started to add support for denormal
numbers, but haven't gotten back to that. # begins Maple comments.
Some comments wrapped, but can be recognized by not being indented.

Jerry

IEEE[2] := proc(d)
# convert exact rational to IEEE double precision (8-byte) rational
approximation
# 11-bit exponent (bias = 1023d = 3FFh)
# 52-bit mantissa w/ 1st digit implied (effectively 53-bit mantissa)
local x,j,a,i,b,sgn;
sgn := sign(d); # strip off sign and process abs(d)
x:=convert(abs(d),rational,16); # providing d already in rational
form is more reliable
if x=0 then a:=0
else
j:=convert(evalf(floor(log[2](x)),200),rational): # convert to binary
a:=2^j:
x:=x-2^j:
for i from j-1 to j-52 by -1 do
if 2^i<=x then
a:=a+2^i;
x:=x-2^i;
b:=1;
else
b:=0;
end if;
if x=0 then i:=j-70 end if;
end do;
if x>=2^(j-53) then # round to 53 bits per IEEE 752
if x>2^(j-53) then
a:=a+2^(j-52);
else
if b=1 then
a:=a+2^(j-52);
end if;
end if;
end if;
# a := trunc(a*2^1074)/2^1074; # 2^-1074 is smallest denormal
number (previous round OK?)
end if;
a*sgn; # restore sign
end proc:
 
D

Dana DeLouis

The binary (& decimal ) approximation to 0.3126 is
1407825243516017 / 4503599627370496
0.3125999999999999889865875957184471189975738525390625

Myrna...If you are interested in the "other" program, there are a few ways
to do it. Here are just two quick examples:

n = 0.3126;

One way to increase the precision of a Machine-Precision number (to an
arbitrary-precision number) is to pad it with zeros in base two. Adding
zeros in base two are almost never zeros in base 10. Here, we get the same
answer.

v = SetPrecision[n, 54]

0.3125999999999999889865875957184471189975738525390625

And if we fully rationalize this number, we get the same rational number.

Rationalize[v, 0]

1407825243516017 / 4503599627370496

However, we are cautioned that we could get different results on different
computer systems.

Just another way would be to convert the machine-precision number to base 2
as best we can knowing there are rounding issues. Then reconstruct the
number in base 2 back to base 10. We get the same number.

v = FromDigits[RealDigits[n, 2], 2]

1407825243516017 / 4503599627370496

And if we divide it out, we get the same number.

N[v, 54]

0.3125999999999999889865875957184471189975738525390625

For the Op, you may be glad to know that even these programs return the same
number, only they return all 17 digits, while Excel only returns 15 digits.
Here we see all 17 digits. Excel rounds this to 15 digits as you've seen.

v = 0.3126 - 0.3124
0.00019999999999997797

What's interesting is if we ask for the Precision of our value 'n'. We get
a strange number.

N[Precision[v]]
15.954589770191003

We can use Excel to see how this number is generated. We use the 53 digits
as Jerry has mentioned.

=LOG(2,10)*53

Returns:
15.954589770191

I am not familiar with Maple.
--
Dana DeLouis
Win XP & Office 2003 (& Mathematica...)


Jerry W. Lewis said:
My mistake. Here is the Maple code; suggestions appreciated. It is
pretty much hammer and tong. I started to add support for denormal
numbers, but haven't gotten back to that. # begins Maple comments. Some
comments wrapped, but can be recognized by not being indented.

Jerry

IEEE[2] := proc(d)
# convert exact rational to IEEE double precision (8-byte) rational
approximation
# 11-bit exponent (bias = 1023d = 3FFh)
# 52-bit mantissa w/ 1st digit implied (effectively 53-bit mantissa)
local x,j,a,i,b,sgn;
sgn := sign(d); # strip off sign and process abs(d)
x:=convert(abs(d),rational,16); # providing d already in rational
form is more reliable
if x=0 then a:=0
else
j:=convert(evalf(floor(log[2](x)),200),rational): # convert to
binary
a:=2^j:
x:=x-2^j:
for i from j-1 to j-52 by -1 do
if 2^i<=x then
a:=a+2^i;
x:=x-2^i;
b:=1;
else
b:=0;
end if;
if x=0 then i:=j-70 end if;
end do;
if x>=2^(j-53) then # round to 53 bits per IEEE 752
if x>2^(j-53) then
a:=a+2^(j-52);
else
if b=1 then
a:=a+2^(j-52);
end if;
end if;
end if;
# a := trunc(a*2^1074)/2^1074; # 2^-1074 is smallest denormal
number (previous round OK?)
end if;
a*sgn; # restore sign
end proc:


Myrna said:
If you meant me, I don't have either one. But I would be interested in
seeing
your function just the same.
Click to expand...
Click to expand...
 
M

Myrna Larson

Thanks for the examples, Dana.

The binary (& decimal ) approximation to 0.3126 is
Click to expand...
1407825243516017 / 4503599627370496
0.3125999999999999889865875957184471189975738525390625

Myrna...If you are interested in the "other" program, there are a few ways
to do it. Here are just two quick examples:

n = 0.3126;

One way to increase the precision of a Machine-Precision number (to an
arbitrary-precision number) is to pad it with zeros in base two. Adding
zeros in base two are almost never zeros in base 10. Here, we get the same
answer.

v = SetPrecision[n, 54]

0.3125999999999999889865875957184471189975738525390625

And if we fully rationalize this number, we get the same rational number.

Rationalize[v, 0]

1407825243516017 / 4503599627370496

However, we are cautioned that we could get different results on different
computer systems.

Just another way would be to convert the machine-precision number to base 2
as best we can knowing there are rounding issues. Then reconstruct the
number in base 2 back to base 10. We get the same number.

v = FromDigits[RealDigits[n, 2], 2]

1407825243516017 / 4503599627370496

And if we divide it out, we get the same number.

N[v, 54]

0.3125999999999999889865875957184471189975738525390625

For the Op, you may be glad to know that even these programs return the same
number, only they return all 17 digits, while Excel only returns 15 digits.
Here we see all 17 digits. Excel rounds this to 15 digits as you've seen.

v = 0.3126 - 0.3124
0.00019999999999997797

What's interesting is if we ask for the Precision of our value 'n'. We get
a strange number.

N[Precision[v]]
15.954589770191003

We can use Excel to see how this number is generated. We use the 53 digits
as Jerry has mentioned.

=LOG(2,10)*53

Returns:
15.954589770191

I am not familiar with Maple.
Click to expand...
 
Top