Results 1 to 3 of 3
  1. #1
    New Lounger
    Join Date
    Jul 2004
    Location
    London, Gtr London, England
    Posts
    10
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Excel calc wrong (excel - office 2003)

    strange problem, we have a spreadsheet with around 7000 rows, when you do a subtotal or even a sum of all the figures in a column which have been set to 2 decimal points we are getting a strange total EG. 3806749.31000001 I have checked all the rows and unable to see this strange 1 in the column. I have also tried to find anythin with 0001 and the find comes up blank. I then tried to find the cell giving problem by selecting the first row in the column and selecting each cell down until the sum in the bottom part of excel showed this strange "1" the cell in which is changed was row 254 but the number in the cell was correct and had nothing in it that might cause a problem, even retyped the number in just in case. Is this a known excel problem??

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

    Re: Excel calc wrong (excel - office 2003)

    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 subtotals to 2 decimal places. This will not remove the rounding error, but it will prevent the error from being displayed.

  3. #3
    New Lounger
    Join Date
    Jul 2004
    Location
    London, Gtr London, England
    Posts
    10
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel calc wrong (excel - office 2003)

    ah that makes sense, I was ripping my hair out. Thanks for the info.

Posting Permissions

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