+ Reply to Thread
Results 1 to 7 of 7

calculation issue?

  1. #1
    Registered User
    Join Date
    02-03-2006
    Posts
    10

    calculation issue?

    Here's the deal:
    I have 4 cells: cell1 = 53711.83
    cell2 = 53711.83
    cell3 = 69.12
    cell4 = 69.12
    When I use a formula = (cell1+cell3-cell2-cell4)/31.25 I should get 0. Instead I get 0.0000000000000836735125631094.

    Cells 1-4 are based on a dsum. I checked these cells and my original data out to 30 decimal places and I can't figure out where my problem is. I have the same setup in several places in my spreadsheet and in some places the formula works as it should, in a few places it doesn't. Where are these extra #s coming from? Any Ideas? Thanks in advance

  2. #2
    Pete_UK
    Guest

    Re: calculation issue?

    Decimal fractions don't always convert easily to binary. Some fractions
    like 1/3 cannot be expressed exactly in decimal. Therefore, small
    rounding errors creep in to some formulae, and yours is one such
    example. Try rounding the results of your dsum to 2 dp using ROUND( )
    and see if this helps matters.

    Hope this helps.

    Pete


  3. #3
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675
    This is a known issue in excel, see here

    http://support.microsoft.com/kb/78113/en-us

  4. #4
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675
    This is a known issue in excel, see here

    http://support.microsoft.com/kb/78113/en-us

  5. #5
    Jerry W. Lewis
    Guest

    RE: calculation issue?

    Excel is clearly documented (Help for "Excel specifications and limits") to
    display no more than 15 decimal digits. To see more you must used the D2D
    function at
    http://groups.google.com/group/micro...06871cf92f8465
    Using that, you will see that neither 53711.83 nor 69.12 can be represented
    exactly.

    This issue common to all software that does binary math (almost all
    software). When you do math with approximate inputs, it should be no
    surprise when the output is also only approximate.

    What you have observed is the binary equivalent of
    1-(1/3)-(1/3)-(1/3) = 1-0.3333-0.3333-0.3333 = 0.0001
    The math is right, but the answer is not the expected zero because of
    necessary initial approximations to 1/3, which cannot be represented exactly
    as a decimal fraction in finite precision.

    Most terminating decimal fractions (including .83 and .12) are
    non-terminating binary fractions ...

    Excel's documented 15-digit limit can be used to predict the magnitude of
    approximation issues. Your problem can then be thought of as
    53711.8300000000????
    69.1200000000000?
    -53711.8300000000????
    -69.1200000000000?
    ---------------------
    0.0000000000????
    which is consistent with Excel's answer
    0.00000000000261
    (the final division was irrelevant to the basic issue and hence was omitted
    here)

    Since the issue is approximation to inputs, not subsequent math, Pete's
    rounding suggestion is entirely reasonable, and does no violence to the
    calculations.

    "JKC" wrote:

    >
    > Here's the deal:
    > I have 4 cells: cell1 = 53711.83
    > cell2 = 53711.83
    > cell3 = 69.12
    > cell4 = 69.12
    > When I use a formula = (cell1+cell3-cell2-cell4)/31.25 I should get 0.
    > Instead I get 0.0000000000000836735125631094.
    >
    > Cells 1-4 are based on a dsum. I checked these cells and my original
    > data out to 30 decimal places and I can't figure out where my problem
    > is. I have the same setup in several places in my spreadsheet and in
    > some places the formula works as it should, in a few places it doesn't.
    > Where are these extra #s coming from? Any Ideas? Thanks in advance
    >
    >
    > --
    > JKC
    > ------------------------------------------------------------------------
    > JKC's Profile: http://www.excelforum.com/member.php...o&userid=31166
    > View this thread: http://www.excelforum.com/showthread...hreadid=524576
    >
    >


  6. #6
    Registered User
    Join Date
    02-03-2006
    Posts
    10

    Thanks

    Thanks for everyone's help on this one.

    JKC

  7. #7
    Pete_UK
    Guest

    Re: calculation issue?

    I like your choice of phrase, Jerry - " ... does no violence to the
    calculations ..."

    Pete


+ 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