Power Query Course in Spanish
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 5 of 5

Thread: Rounding Errors

  1. #1
    New Member
    Join Date
    Apr 2002
    Posts
    1
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I am computing annual budget numbers in Excel (whole number amounts) that have to be
    spread into 12 months based on a specific monthly spread.
    The Budget System that I am uploading these numbers to lops off any decimal amount
    and then adds the 12 months together. Of course, this causes a rounding
    error and the Annual total in Excel doesn't match the resummed value in my Budget System.

    I am trying to head this problem off in Excel by only uploading monthly, whole numbers
    by using the ROUND formulas when I spread the Annual amount into 12 months.
    Inevitably, as in the budget system, the sum of the months has an absolute variance
    from the Annual base number of 1 to 6 usually. Is there a way to force a match to the base without pickout out random months and adding back the 4 or 5. The numbers are
    large enough that the 4 or 5 variance is insignificant but over 10,000 entries, the report numbers between Excel and the Budget System vary significantly. Any suggestions?

  2. #2
    Board Regular Steve Hartman's Avatar
    Join Date
    Feb 2002
    Location
    Houston,Texas
    Posts
    417
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Any rounding you do will introduce errors. That is just a fact of life. You said the Budget system "lops off" the decimals. I assume you mean it just takes the integer portion of the number without rounding. To get the two to match, you will have to do the same in Excel without rounding by using the =INT() function. Of course this will introduce a whole new set of errors but at least both sets of numbers will agree.

  3. #3
    MrExcel MVP lenze's Avatar
    Join Date
    Feb 2002
    Location
    Helena, MT
    Posts
    13,690
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Not sure if this will help, but check under Tools>Options>Calculations and check the box "Percision as Displayed" This will force excel to base it's calculations only on values as they appear on screen. This usually will prevent most rounding errors.

  4. #4
    Board Regular zacemmel's Avatar
    Join Date
    Apr 2002
    Location
    Redmond, WA
    Posts
    636
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    When working with precise numbers for financial reasons, do not use Excel. Excel is known to have floating point errors when rounding and calculating. This can be circumvented by using precision as displayed, but that doesn't always work.

  5. #5
    Board Regular Barry Katcher's Avatar
    Join Date
    Feb 2002
    Location
    Dog Beach, Florida. Yeaahh!
    Posts
    4,053
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    If your budget program does, in fact, lop off the decimals AND if the sum of the columns does not include the lopped off decimals, try using:

    =FLOOR(value,1)

    to round down to the nearest integer


    Barry-

    Photo Restoration/Enhancement

    http://www.smiledogproductions.com
    click below for detour


Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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