Results 1 to 4 of 4
  1. #1
    New Lounger
    Join Date
    Jun 2003
    Posts
    22
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Decimal Places Problem (Excel 2003/SP2)

    I have a strange decimal place problem.

    I have about 2,400 rows of numbers which I know are to 2 decimal places, when I SUM the column and display the number of decimal places to 12, the total value is displayed to 9 decimal places???

    For example 67910.399999999000

    Any ideas

    Regards

    - Maurice

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Decimal Places Problem (Excel 2003/SP2)

    Excel stores numbers in binary format, but displays them in decimal format. When converting numbers from decimal to binary and from binary to decimal, very small rounding errors can occur because a number that can be expressed exactly in one system can't always be expressed exactly in the other system. For example, the decimal number 0.1 corresponds to binary 0.0001100110011...
    With single numbers, the rounding error is so small that you won't see it, but if you add lots of numbers, the rounding errors can accumulate to a noticeable value.
    You can set the number format for the sum to 2 decimal places. This will not remove the rounding error, but it will prevent the error from being displayed.
    Or you can use a formula such as
    <code>
    =ROUND(SUM(A1:A2400),2)
    </code>
    This will round the result of the SUM formula to 2 decimal places: 67910.40

  3. #3
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Perth, Western Australia, Australia
    Posts
    190
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Decimal Places Problem (Excel 2003/SP2)

    Maurice,

    Are the 2 decimal place numbers rounded to 2 palces, or have they been keyed in as 2 dcimal places only?

  4. #4
    New Lounger
    Join Date
    Jun 2003
    Posts
    22
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Decimal Places Problem (Excel 2003/SP2)

    Originally I imported the figures and spent some time trying to find the line that caused the funny total with 9 decimal places.

    I then setup a test spreadsheet to repeat the problem by entering a dozen lines with two decimal places and then copying and pasting until I got the 2,400 lines.

    Regards

    - Maurice

Posting Permissions

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