Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel 2003 FAILS, but Excel 2000 SUCCEEDS ??? | Excel Discussion (Misc queries) | |||
How do I isolate my Excel server (automation) from other Excel instances? | Excel Discussion (Misc queries) | |||
Excel 2002 and 2000 co-install. Control Which Starts ? | Excel Discussion (Misc queries) | |||
VB Automation is Whacking out my Excel Environment | Excel Discussion (Misc queries) | |||
Excel - Time calculation | Excel Discussion (Misc queries) |