Results 1 to 5 of 5
  1. #1
    3 Star Lounger
    Join Date
    May 2002
    Posts
    398
    Thanks
    1
    Thanked 0 Times in 0 Posts
    I have a question on what is the "correct" rounding convention.

    In Excel, 24.15 becomes 24.2 when rounded or when formatted to 1 decimal place.

    Another program I am using rounds the numbers somewhat differently. When rounding to the tenth decimal place, everything from x.0 up to and equal to x.5 is rounded down. Everything above x.5 is rounded up.
    For example, 24.15 is rounded down to 24.1, but 24.150000001 is rounded up to 24.2.

    As long as I am aware of it, it's ok, but I'm curious as to whether there is an "official" convention.
    thanks!

  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    6,379
    Thanks
    207
    Thanked 829 Times in 762 Posts
    J.E.

    It has always been my understanding that .5 goes up to the next digit, i.e.,
    .05 = .1
    .005 = .01, etc.
    and < .5 goes down, i.e.,
    .4 = 0
    .014 = .1
    .0114 = .011, etc.

    Excel follows this method.

    However, beware of the formatting trap!
    Formatting a number to one decimal place appears to round it but
    calculations will use the whole number!

    In the following example Col A & C are formatted as 2 Decimal Places
    Col B is formatted as 1 Decimal Place
    B1 formula = "=A1", ect
    C1 formula = "=Round(A1,1)"

    You can see the difference in the sums!

    RG
    Attached Images Attached Images
    May the Forces of good computing be with you!

    RG

    VBA Rules!

    My Systems: Desktop Specs
    Laptop Specs


  3. #3
    Super Moderator BATcher's Avatar
    Join Date
    Feb 2008
    Location
    A cultural area in SW England
    Posts
    2,848
    Thanks
    19
    Thanked 110 Times in 104 Posts
    Quote Originally Posted by jepalmer View Post
    When rounding to the tenth decimal place, everything from x.0 up to and equal to x.5 is rounded down. Everything above x.5 is rounded up.
    You might be getting problems with the precision in which numbers are held and/or the conversion between the internal 'binary' form and the decimals which humans understand! "Tenth decimal place" may be outside the capabilities of that program...

    A couple of Wikipedia articles: Accuracy and Precision, and Floating Point, and something fascinating from Microsoft: INFO: Precision and Accuracy in Floating-Point Calculations.

    Knew you'd enjoy it!
    BATcher

    Dear Diary, today the Hundred Years War started ...

  4. #4
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts
    There are various rounding techniques. Some round up on even digits and down on uneven digits (I forgot which way around). So:

    0.15 becomes 0.1
    0.25 becomes 0.3
    0.35 become 0.3
    0.45 becomes 0.5

    Excel rounds up if the next digit is 5 or more.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  5. #5
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    6,379
    Thanks
    207
    Thanked 829 Times in 762 Posts
    Quote Originally Posted by BATcher View Post
    You might be getting problems with the precision in which numbers are held and/or the conversion between the internal 'binary' form and the decimals which humans understand! "Tenth decimal place" may be outside the capabilities of that program...
    You're absolutely correct. This has been a know problem with all spreadsheets I've ever used starting w/VisaCalc (showing my age).
    I still don't understand why Microsoft doesn't provide a Binary Coded Decimal (BCD) option in Excel. With this form of encoding of numbers there is true representation of base 10 decimals. Which IMHO is a basic necessity for calculations involving dollars and cents where absolute accuracy is required (that's the old accountant in me speaking...).

    RG
    May the Forces of good computing be with you!

    RG

    VBA Rules!

    My Systems: Desktop Specs
    Laptop Specs


Posting Permissions

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