Error in Excel

C

ComputerLand

I try to convert some decimal number to hex number in specific way.

430.00000 -> 39c0
430.01250 -> 39c1
430.02500 -> 39c2
.....
470.00000 ->4640

For that purpose I fill column A starting with 430.00000 to 470.00000, step
0.0125. There is onlu 45 excact number !!!! Row 45 has value 430.5375 and
row 46 - 430.549999999999.
Converted, value of 430.00000 has hex value 39c0, 430.5375 = 39eb and next
value 430.549999999999 - has the same value - 39eb

I know that it's easy to fix this bug for my purpose but I also think that I
must have confidence to excel calculations. It's silly to check every result
!!!

If anybody want, I can send him screenshoot(s) or all file.

Any comment?

Sasha
 
P

Pete_UK

Try to convert 0.1 (decimal) to binary - you will find that it is a
recurring number, in the same way that 1/3 = 0.333333333333 etc
forever. So, some fractions in decimal can't be expressed exactly in
binary (or hex). You seem to be using 4 bytes in fixed-point, with 3
bytes representing the integer part x 10, and 1 byte for the fraction.
Thus you only have 8 bits available to represent the fraction, so you
have a loss of precision and accuracy.

Pete
 
J

Jerry W. Lewis

Try your algorithm in any package you like; you will likely get the same
result. Excel and almost every other general purpose software does binary
math, where .0125 is a non-terminating decimal fraction that can only be
approximated. Excel and almost every other general purpose software
follows IEEE 754 standard for double precision to define the level of
approximation.

Per its documentation, Excel will not display more than 15 decimal digits
for a number, but you can use the D2D function at
http://groups.google.com/group/microsoft.public.excel/msg/b106871cf92f8465
to see more more than 15 decimal digits of the actual values that Excel is
calculating with. Similarly, the D2B function from that location can display
a binary floating point representation of the number using the full IEEE
double precision.

As for your concern about Excel calculations, I have never seen an example
where basic arithmetic in Excel was not performed correctly. This stands to
reason, since basic arithmetic is almost certainly performed by the CPU (why
would any software designer re-invent the wheel).

Jerry
 
B

Bernard Liengme

You have two good answers. I would add to Jerry's comment "As for your
concern about Excel calculations, I have never seen an example where basic
arithmetic in Excel was not performed correctly", however, the 'rounding'
problem can result in a false answer. IF, for example, you test to see if
B10 is zero when B10's value comes from a calculation, you could get a false
negative. B10 may evaluate to a small value such as 1.23E-15.
Conclusion:test is ROUND(B2,12)=0 or if ABS(B10)<1E-14
best wishes
 
G

Gonzalo

Hello,

I believe I've found an arithmetic error in Excel and would like you to
verifiy.

Put into a cell, say B2, the following operation:
=799.5-799.5+762.6+22906-7740+23412-6260.85-10547.12+6867+1168.5-17461.24-11694.86
The result is 1412.03
Then, on B3 put =B2-1412.03
The result on B3 is -0.00000000000477484718430787 instead of zero.
I have checked this on Excel 97 and Excel 2002 on two different computers,
one with Intel celeron 800 Mhz and the other Pentium 4 2,8 Mhz.
 
J

JE McGimpsey

Not so much an arithmetic error as a limitation of double precision
floating point math, which XL and most other spreadsheets use.

Much as 1/3 cannot be exactly represented as a decimal number, so most
numbers cannot be exactly represented in a finite number of binary
digits. Therefore rounding errors can creep in.

See

http://cpearson.com/excel/rounding.htm
 
J

Jerry W. Lewis

No, it is not an error.

Try the calculation in any package you choose, you will likely get the same
answer. Excel and almost every other general purpose software does binary
math, where .6, .85, .12, .24, .86 and .03 are a non-terminating binary
fractions that can only be approximated. The only decimal fractions with <=2
digits that can be represented exactly in binary are .0, .25, .5, and .75.
Excel and almost every other general purpose software follows IEEE 754
standard for double precision to define the level of approximation.

IEEE double precision can only guarantee 15 correct digits in the
approximation to a number (see Help for "Excel specifications and limits"
subtopic "Calculation specifications"). Thus the necessary approximations to
10547.12, 17461.24, and 11694.86 are only assured of 10 decimal place
accuracy. That your final result is still zero at the 11th decimal place is
a happy accident of the particular numbers you chose.

Given the necessary approximations to your inputs, the result of the
calculation should be exactly -0.4774847184307873249053955078125E-12, which
Excel correctly reports to its documented 15 digit limit as
-4.77484718430787E-12.

No approximation is needed for integers with no more than 15 digits, so if
you multiplied all your numbers by 100, then you would get the expected zero
result from
=79950-79950+76260+2290600-774000+2341200-626085-1054712+686700+116850-1746124-1169486-141203.
Otherwise, you need to be aware of the possible impact of accumulating
approximations. This is sometimes controled by rounding appropriately.

You may find the VBA functions at
http://groups.google.com/group/microsoft.public.excel/msg/b106871cf92f8465
to be useful for examining the impact of finite precision binary
approximation to decimal fractions.

Jerry
 
Top