Results 1 to 4 of 4

20061201, 09:11 #1
 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

20061201, 10:54 #2
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 30 Times in 30 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

20061201, 10:55 #3
 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?

20061201, 12:22 #4
 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