About Calculation of Excel

[Guest]

Actually I found this problem recently and don't know why this could happen.
I did simple minus calculation of these two values: (-0.365)-(-0.3575)
and excel shows -0.0075000000000000100000000.
It is not exactly -0.0075.
Do I need to change some settings or ....?

Thank you

 

[Gordon]

Actually I found this problem recently and don't know why this could
happen. I did simple minus calculation of these two values:
(-0.365)-(-0.3575)
and excel shows -0.0075000000000000100000000.
It is not exactly -0.0075.
Do I need to change some settings or ....?

Thank you

Set it to 4 places of decimal - do you *need* 25 places?

 

[Norman Jones]

Hi Sean,

See chip Pearson's page on rounding an precision in Excel at:

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

 

[Guest]

Actually I found this problem recently and don't know why this could happen.
I did simple minus calculation of these two values: (-0.365)-(-0.3575)
and excel shows -0.0075000000000000100000000.
It is not exactly -0.0075.
Do I need to change some settings or ....?

Thank you

----------------------------

No -- what you're seeing is how computers work. Basically Excel, and
most spreadsheets store their numbers and do math in Binary while you
punch in numbers and think in Decimal. There is not an exact conversion
between the two (for fractions) so errors occur down in the least
significant digit.

The number 0.1 in decimal for example is an irrational number infinitely
long in binary. A computer however only carries and calculates with a
finite number of bits so some rounding occurs.

Three ways around this exist in theory:

1) Use more digits of precision in your application. You'll still have
an error but it will be a lot more digits further down in the decimal.
I don't know that you can tell Excel to use double precision -- I think
you're stuck with 16 digits or so. With direct programming languages
you generally can select different numbers of bytes of precision to work in.

2) Use integer arithmetic only. Covert all numbers to integers and work
with those. If you do things like dividing though that's very hard to
manage and you end up with rounding errors anyhow.

3) Have the computer programmed to operate in decimal rather than
binary. This uses storage rather inefficiently and is very slow
compared to binary so it generally isn't used except in cases where it's
really important for some reason.

Bill

 

[Jerry W. Lewis]

As Bill Martin has noted, these numbers have no exact representation in
binary.

The IEEE double precision approximation to 0.365 is
1643813863990231/4503599627370496 which in decimal is
0.3649999999999999911182158029987476766109466552734375.

The IEEE double precision approximation to 0.3575 is
6440147467139809/18014398509481984 which in decimal is
0.357499999999999984456877655247808434069156646728515625

Do the math, the correct difference between these approximate inputs is
-0.007500000000000006661338147750939242541790008544921875 which Excel
correctly reports (to its documented 15 figure limit) as
-0.00750000000000001

The simplest way to think about this is to recognize that digits beyond
the 15th figure may be unexpected due to binary approximation so your
problem becomes
0.357500000000000??
-0.365000000000000??
--------------------
0.007500000000000??
consistent with Excel's reported value of
0.00750000000000001

When you are adding numbers that have no more than 4 decimal places,
then anything beyond the 4 decimal place in the final result is binary
junk. Therefore simply round to 4 decimal places to eliminate binary
junk without doing violence to the computation.

Jerry

 

[Guest]

Gentlemen,
Thank you for your help.
Although I'm not a professional programmer, I'm kind of getting the idea.

Sean