Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Sean
 
Posts: n/a
Default About Calculation of Excel

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
  #2   Report Post  
Gordon
 
Posts: n/a
Default

Sean wrote:
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?


  #3   Report Post  
Norman Jones
 
Posts: n/a
Default

Hi Sean,

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

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


---
Regards,
Norman



"Sean" wrote in message
...
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



  #4   Report Post  
Bill Martin -- (Remove NOSPAM from address)
 
Posts: n/a
Default

Sean wrote:
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
  #5   Report Post  
Jerry W. Lewis
 
Posts: n/a
Default

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.364999999999999991118215802998747676610946655273 4375.

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

Do the math, the correct difference between these approximate inputs is
-0.007500000000000006661338147750939242541790008544 921875 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

Sean wrote:

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




  #6   Report Post  
Sean
 
Posts: n/a
Default

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

Sean



"Sean" wrote:

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

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Excel 2003 FAILS, but Excel 2000 SUCCEEDS ??? Richard Excel Discussion (Misc queries) 2 May 13th 23 11:46 AM
How do I isolate my Excel server (automation) from other Excel instances? Joseph Geretz Excel Discussion (Misc queries) 5 July 19th 13 03:18 PM
Excel 2002 and 2000 co-install. Control Which Starts ? cnuk Excel Discussion (Misc queries) 2 January 17th 05 09:07 PM
VB Automation is Whacking out my Excel Environment Joseph Geretz Excel Discussion (Misc queries) 2 December 29th 04 04:49 PM
Excel - Time calculation Leyland Excel Discussion (Misc queries) 1 November 30th 04 08:43 PM


All times are GMT +1. The time now is 09:30 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"