rounding errors

[Guest]

Hi

I am calculating the percentage change between two indices and Excel seems
to have calculated this incorrectly. If I use the ROUND() function to round
to two decimal places the answer in Excel is 3.25 (correct). If i round to 1
decimal place in Excel the answer is 3.2 (INCORRECT). Ofcourse, the answer
should be 3.3. Anyone else aware of this? If so, are there specific instances
in which this happens? The calculation in question uses X=82.6 and Y=80 in
formula
((X-Y)/Y)*100

Thanks
Martin

 

[Guest]

It is not clear how this issue is causing Excel to crash (the topic of this
newsgroup)

Excel (and almost all other general purpose software) does binary math.
Most terminating decimal fractions (such as .6) are non-terminating binary
fractions that can only be approximated in binary (just as 1/3 can only be
approximated as a decimal fraction). The IEEE standard approximation to 82.6
is 82.599999999999994315658113919198513031005859375, which when used in your
calculation results in a final value of
3.24999999999999733546474089962430298328399658203125, which Excel correctly
reports to its documented 15 digit limit as 3.24999999999999. Naturally,
3.24999999999999 rounds down to 3.2.

A more robust way to do the calculation is
=X*100/Y-100
since X*100 is an integer that can be exactly represented in binary.

You can use the VBA functions at
http://groups.google.com/group/microsoft.public.excel/msg/b106871cf92f8465
to explore binary approximations to floating point numbers.

Jerry