# Thread: Decimal Places Problem (Excel 2003/SP2)

1. ## 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. ## 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. ## 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. ## 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
•