Re: A wrong calculation by excel

This so-called error is the result of the computer converting decimals
numbers (base 10) to binary (base 2)
See one or more of these
IEEE 754
Floating-point arithmetic may give inaccurate results in Excel
http://support.microsoft.com/kb/78113/en-us
(Complete) Tutorial to Understand IEEE Floating-Point Errors
http://support.microsoft.com/kb/42980
What Every Computer Scientist Should Know About Floating Point
http://docs.sun.com/source/806-3568/ncg_goldberg.html
http://www.cpearson.com/excel/rounding.htm
Visual Basic and Arithmetic Precision
http://support.microsoft.com/default.aspx?scid=http://support.microsoft.com:80/support/kb/articles/Q279/7/55.ASP&NoWebContent=1

--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"Captain" <Captain@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:58B51CF9-1485-4C90-AAE5-0352062B7BBA@xxxxxxxxxxxxxxxx
Your problem is exactly the same as mine. I found that there was an error
in
the 13th decimal place when subtracting exactly $1,015.00 from $1,183.30!
Get around this error by writing a formula that accepts a calculation as
correct if it matches the expected answer to within $.01 or within .01%,
etc.
example: IF(AND(A1<B1+.01,A1>B1-.01),"OK","error").
--
E. Polizzi


"BobD" wrote:

I am using Excel 2003 Professional Edition. If you include the following
formula into a cell, it produces a result of 8.74999999999999 if you
expand
the decimal places to 16. The correct answer is 8.75. The formula is
((17.15-17)*10/6+17)-((8.3-8)*10/6+8). This only happens if you use 17.15
and
8.3. I came across this when using a process to convert times to
decimals
and then subtract one from the other. Anyone have any ideas?
--
BobD


.

Relevant Pages

  • Re: Excel is unusable for finance or accounting.
    ... The problem isn't really in the floating-point representation of numbers, per se, or the ... Could Exel do floating-point ... The processor can't do decimal math natively, so it'd have to be done in software, ... handle simple fractions and decimals (see thread 'Glaring Excel calc ...
    (microsoft.public.excel)
  • Re: Gentler Decimal Floating-Point
    ... decimal FLOATING-POINT arithmetic and, despite frequent requests, you ... financial programmers wanted fixed-point with overflow detection, ... NONE of them wanted decimal floating-point. ... |> migrated towards floating-point for decimals. ...
    (comp.arch.arithmetic)
  • Re: Working with floating point values
    ... That's what I've always done in the past, but decimals are *much* slower to ... complex calculations thousands of times each, ... decimals and doubles is quite noticeable. ... >> nature of floating-point calculations to produce values like ...
    (microsoft.public.dotnet.languages.csharp)
  • Re: Floating point rounding error
    ... never bothered myself as to how decimals were represented as binaries. ... The representation of floating-point numbers is implementation-defined. ... IEEE 754 is common but not universal. ... Is this rule of representing decimals in binary applicable only to ...
    (comp.lang.c)
  • Re: strings (dollar.cents) into floats
    ... But what use is there for floats, ... cannot be exactly represented by decimals. ... Indeed I believe the accountant's Swiss army knife, the Excel spreadsheet, uses floating-point numbers exclusively. ... What you say about floating-point have speed advantages is true, but you go too far in claiming that decimal arithmetic is mandatory for monetary calculations. ...
    (comp.lang.python)