+ Reply to Thread
Results 1 to 5 of 5

Incorrect Simple Math

  1. #1
    Registered User
    Join Date
    08-11-2004
    Location
    Webb City, Missouri, USA
    Posts
    2

    Question Incorrect Simple Math

    61.48-61.42=0.0599999999999952

    Why?

    This happens frequently in various formulas.

    66555.54-66081.35=474.189999999988

    What is going on? Surely I'm not the only one that has noticed this?

    _______________
    Press F1 for help.
    F
    1
    Now what?

  2. #2
    Gary''s Student
    Guest

    RE: Incorrect Simple Math

    This is simply round-off error. The result of the calculation is not exact
    and corresponds to the nearest value that can be represented by the IEEE
    number format that Excel uses.
    --
    Gary's Student


    "mklalli" wrote:

    >
    > 61.48-61.42=0.0599999999999952
    >
    > Why?
    >
    > This happens frequently in various formulas.
    >
    > 66555.54-66081.35=474.189999999988
    >
    > What is going on? Surely I'm not the only one that has noticed this?
    >
    >
    > --
    > mklalli
    >
    >
    > ------------------------------------------------------------------------
    > mklalli's Profile: http://www.excelforum.com/member.php...o&userid=12973
    > View this thread: http://www.excelforum.com/showthread...hreadid=483140
    >
    >


  3. #3
    Registered User
    Join Date
    08-11-2004
    Location
    Webb City, Missouri, USA
    Posts
    2
    So is there a fix?

  4. #4
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    13,749
    What kind of "fix" do you require?

    Decimal fractions (even finite ones) usually have infinitely long representations when converted to binary. It just isn't possible to represent an infinitely long number within the finite confines of the computer's memory.

    Roundoff error has been around for a long time and will likely always be a part of using a computer for calculations. So the real question is how to best deal with roundoff error (recognizing that it can often only be minimized, not eliminated), which will depend on what you need. Options to consider:

    ROUND functions or number formats
    Precision as displayed
    Convert to integer (Assuming your numbers are EXACT to two decimals and not themselves rounded, an operation like (61.48*100-61.42*100)/100 can yield 0.0600000000000000)

    Consider reviewing http://www.cpearson.com/excel/rounding.htm or any other article or text that discusses such roundoff errors. This is an issue that is fairly well documented.

  5. #5
    Jerry W. Lewis
    Guest

    Re: Incorrect Simple Math

    Almost all general purpose software (including Excel) does binary math.
    In binary, most decimal factions have no exact representation (just as
    1/3 has no exact decimal representation) and must be approximated. When
    you do math with approximate inputs, it should be no surprise that the
    output is only approximate.

    The binary approximations to your input numbers are
    61.47999999999999687361196265555918216705322265625
    -61.4200000000000017053025658242404460906982421875
    ---------------------------------------------------
    0.05999999999999516830939683131873607635498046875

    66555.539999999993597157299518585205078125
    -66081.35000000000582076609134674072265625
    -------------------------------------------
    474.189999999987776391208171844482421875

    Do the math, the answers are correct, given the input numbers, and Excel
    correctly reports these answers to its documented limit of 15 decimal
    digits.

    You can easily construct similar examples involving finite decimal
    precision representation of numbers that are non-terminating decimals in
    base 10. It is an unavoidable fact of life that some numbers cannot be
    exactly represented with a finite number of decimals (or a finite number
    of binary bits).

    Your options are to either not use such numbers (for instance do integer
    math) or structure your calculations such that the inherrent limitations
    of finite precision are not a problem (for instance round results before
    comparisons).

    Earlier this year, I posted VBA code to display 28 decimal figures of
    the binary representation of floating point numbers
    http://groups.google.com/group/micro...fb95785d1eaff5

    But it is not necessary to go that deep to predict the level of rounding
    that you may need to do. Simply follow through on Excel's documented
    limit of 15 decimal digits. Thus you can think of your equations as
    61.4800000000000???
    -61.4200000000000???
    0.0600000000000???
    which is consistent with
    0.0599999999999952

    Similarly
    66555.5400000000??
    -66081.3500000000??
    ---------
    474-1900000000??
    which is consistent with
    474.189999999988

    Jerry

    mklalli wrote:

    > 61.48-61.42=0.0599999999999952
    >
    > Why?
    >
    > This happens frequently in various formulas.
    >
    > 66555.54-66081.35=474.189999999988
    >
    > What is going on? Surely I'm not the only one that has noticed this?



+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1