Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Why does Excel XP say that 25-25.08+0.08 = 1.706975E-15 instead o.
Why does excel say that 25-25.08 +0.08= 1.706975E-15 instead of Zero? Are
there situations that excel typically makes calculation errors I should be aware of? |
#2
|
|||
|
|||
http://cpearson.com/excel/rounding.htm
-- Regards Ken....................... Microsoft MVP - Excel Sys Spec - Win XP Pro / XL 97/00/02/03 ---------------------------------------------------------------------------- It's easier to beg forgiveness than ask permission :-) ---------------------------------------------------------------------------- "Tarek" wrote in message ... Why does excel say that 25-25.08 +0.08= 1.706975E-15 instead of Zero? Are there situations that excel typically makes calculation errors I should be aware of? |
#3
|
|||
|
|||
Here is the short(er) version:
Computers can't do math in base 10. They convert everything to binary, do the calculations, and then convert the results back to decimal for display. The main problem is that a number with small number of decimal places in base 10 may have a large or even infinite number of decimal places in binary, and vice versa. So precise, rational numbers can become rounded off during one or both conversions. For example, .2 in decimal becomes ..00110011001100110011 repeating infinitely in binary. Excel will chop it off to a reasonable length at conversion. Excel uses many tricks to minimize the impact this rounding has, so most of the time you won't see any discrepencies. Even when they are there, these small discrepencies are usually not a problem. No one cares if your sales tax calculation is one trillionth of a penny off, because you are going to round it to the nearest penny away. Where you may see a problem is in formulas that rely on comparisons. Such as IF(A1=0... or A10 or a VLOOKUP or MATCH function. If you are having problems with functions like these, you will need to modify some of your formulas to compensate, such as =ROUND(25-25.08+.08,2) Tim C "Ken Wright" wrote in message ... http://cpearson.com/excel/rounding.htm -- Regards Ken....................... Microsoft MVP - Excel Sys Spec - Win XP Pro / XL 97/00/02/03 ---------------------------------------------------------------------------- It's easier to beg forgiveness than ask permission :-) ---------------------------------------------------------------------------- "Tarek" wrote in message ... Why does excel say that 25-25.08 +0.08= 1.706975E-15 instead of Zero? Are there situations that excel typically makes calculation errors I should be aware of? |
#4
|
|||
|
|||
See
http://www.mcgimpsey.com/excel/pennyoff.html and http://cpearson.com/excel/rounding.htm In article , "Tarek" wrote: Why does excel say that 25-25.08 +0.08= 1.706975E-15 instead of Zero? Are there situations that excel typically makes calculation errors I should be aware of? |
#5
|
|||
|
|||
For =25-25.08+0.08 I get 1.706968E-15. If you are getting 1.706975E-15
then some of your inputs are not exactly as described (probably the result of other calculations). The following explains the 1.706968E-15 result, from it you should see the principle that would extend to your specific problem. 0.08 (like most decimal fractions) has no exact binary representation, just as 1/3 has no exact decimal representation. When you do math with approximate inputs, the answer is naturally only approximate. A direct analogy would be if you had a decimal computer that carried only 4 decimal digits. In that case, 25-(25+1/3)+(1/3) would be calculated as 25 -25.33 ----- -0.33 +0.3333 ------- 0.0033 instead of zero, for obvious and correct reasons. Per the IEEE standard for double precision binary representation (used by Excel and almost all other general purpose computing hardware and software), the binary approximation to 25.08, converted back to decimal, is 25.0799999999999982946974341757595539093017578125 The binary approximation to to 0.8, converted back to decimal, is 0.080000000000000001665334536937734810635447502136 23046875 Do the math; the correct answer is 1.70696790036117818090133368968963623046875E-15 which Excel correctly reports to 15 figures (Excel's documented limit -- cf. Help for "Excel specifications and limits" subtopic "Calculation specifications") It is not necessary to do all the decimal/binary conversions, just follow the implications of the documented 15 digit accuracy. Your problem then becomes: 25 -25.0800000000000???????? ------------------------- -0.0800000000000???????? +0.0800000000000000????? ------------------------ 0.0000000000000???????? which is entirely consistent with Excel's result of 0.000000000000001706968 For addition and subtraction, the simplest approach to removing the impact of binary approximation to input numbers is to round the result to the most number of decimal places used (2 in this case). Alternately, you could do integer arithmetic (no approximation involved in converting integers to binary) =(2500-2508+08)/100 will return the expected zero. Jerry Tarek wrote: Why does excel say that 25-25.08 +0.08= 1.706975E-15 instead of Zero? Are there situations that excel typically makes calculation errors I should be aware of? |
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) | |||
Difference in number of Excel NewsGroups | Excel Discussion (Misc queries) | |||
sharing/using/saving Excel 2002 files in Excel 2003 | Excel Discussion (Misc queries) | |||
Excel 2002 and 2000 co-install. Control Which Starts ? | Excel Discussion (Misc queries) |