Results 1 to 9 of 9
  1. #1
    2 Star Lounger
    Join Date
    May 2002
    Posts
    155
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Spurious decimal places (Excel 2000 - SR1)

    I have built a fairly complicated - about 10 separate worksheets - model to generate a set of accounts, but I am troubled by the appearance of spurious decimal places within some cell values.

    The origin and travel of my data, and the appearance fo the unwanted decimals in my model, is as follows:-

    My data is imported via a comma delimited file exported from a standard accounts package (Sage) where all data is entered in a fixed 2-decimal place format. All account balances have 2 decimal places therefore.

    The comma delimited file is opened in Excel and saved as a workbook, and then copied into one of the sheets in my model.

    The balances are then picked up from this sheet and distributed around the model according to formulas which just state

    =sheet reference, cell reference

    Typically the balances are distributed into a whole series of tables. Each individual table will then be totalled.

    The table totals are then picked up by other sheets which enter the totals into Profit and Loss Accounts and Balance Sheets, which themselves include formulas to sum these subtotals.

    So far, so good!!

    At this stage everything appears fine. It is also important that I emphasise that none of the formulas anywhere in the model include a rounding funcition, and none of the calculations the model performs are multiplication or division. All formulas are addition or subtraction only.

    Now things get a bit wierd!

    My next step involves producing graphs to illustrate the data in my accounts. Because I need to retain prior month information, I use a macro to populate a dataset table, which is itself used to generate the graphs. I need the macro so when updating the dataset, the update process doesn't overwrite last months figures. Instead the macro checks the current month number from another worksheet in my model, and hence populates the correct cell in the dataset table.

    Thanks for your perseverance in reading this far! You have now reached my query!!

    Why does the data in my dataset table contain numerous spurious decimal places?

    All my macro does is to copy the result displayed in a particular cell (eg Monthly profit) elsewhere in the model, and as I have explained, the result displayed in that cell is to 2 decimal places, just as all the preceeding data which 'created' it is to 2 decimal places.

    For example, I have a total figure on one of my sheets of 228.68. When my macro picks this up and places it in the dataset table, it appears as 228.684999999994. I have another total on one of my sheets of 6489.63. This one appears in my dataset table as 6489.63000000004.

    If anyone can help me fix this, or better understand what is going on, I'd be extremely grateful.

    Don't let me down - this is my inaugral posting, after all!!

    Thanks in anticipation

    Neil

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

    Re: Spurious decimal places (Excel 2000 - SR1)

    Excel stores numeric values as binary numbers with a finite precision. Decimal numbers often can't be represented exactly in binary notation with finite precision, just as for instance 2/3 can't be represented exactly in decimal notation with finite precision: 2/3 = 0.6666666666666666... So, inevitably, rounding errors occur. (Cf. 2/3 ~ 0.6667) For individual numbers, these rounding errors are insignificant. But when you add or subract many numbers, the errors accumulate and can become visible.

    So essentially, there is nothing to worry about. You can format all numbers in your worksheets with 2 decimal places. This doesn't alter the way they are stored, only how they are displayed.

  3. #3
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Spurious decimal places (Excel 2000 - SR1)

    I see that HansV has given you an explanation of why this is happening. I would like to add a little to what he has said. If this is financial data, then the precision problem that he talks about can cause rounding errors when you try to add up those values. There are a couple of things that you can do to get around this:

    1- If you want the precision of all of the values in the workbook to be rounded to the number of places that are displayed (in other words, if you want 228.684999999994 rounded to 28.68 when it is displayed with two decimal places), then you can select Options from the Tools menu, click on the Calculation tab and put a check next to "Precision as displayed" in the Workbook Options section.

    2- If you need only some cells to be rounded, then you can use the ROUND() function around the formula in those cells to round the result to the number of places desired.
    Legare Coleman

  4. #4
    2 Star Lounger
    Join Date
    May 2002
    Posts
    155
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: Spurious decimal places (Excel 2000 - SR1)

    Thank you Legare and Hans for taking the trouble to reply.

    However, I think the key point of my query got lost in my over-detailed scenario!

    My real question is how can these 'spurious' decimal places arise at all when the source data was originally entered as 2 decimal places, with no ability to vary this, and the only calculations performed on the data once it gets to my Excel model is addition and subtraction?

    Any thoughts anyone?

    Neil

  5. #5
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Spurious decimal places (Excel 2000 - SR1)

    neil, I hope MSKB 214118 and Chip Pearson's Rounding Error Article will provide more detail to help you understand Hans' and Legare's explanation and comments.
    -John ... I float in liquid gardens
    UTC -7ąDS

  6. #6
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Spurious decimal places (Excel 2000 - SR1)

    I'm sure that the references that JohnBF gave you will give a more detailed explanation, but to make a long story short, Excel decimal values are kept as binary floating point numbers. Many decimal values do not convert exactly to binary floating point, they become repeating binary fractions. So, even if you enter the value as an exact decimal number, it may convert to a repeating binary fraction.
    Legare Coleman

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

    Re: Spurious decimal places (Excel 2000 - SR1)

    Here is a screenshot that demonstrates what happens. It was made in Access, because I had an existing example there, but the principle is the same in Excel.
    The column Amount contains numbers exactly as they were entered - max 2 decimals. The column RunSum contains a running total - only additions are used. As you will see, at a certain point the running total displays extra digits, although all individual numbers used to calculate it have 2 decimals. This is due to accumulating extremely small rounding errors.
    It is very unlikely that the error will ever become so large that the result rounded to 2 decimals will become inaccurate.
    Attached Images Attached Images

  8. #8
    2 Star Lounger
    Join Date
    May 2002
    Posts
    155
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: Spurious decimal places (Excel 2000 - SR1)

    Thanks for your help, everyone. I now understand what's going on!

    Neil

  9. #9
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Youngstown, Ohio, USA
    Posts
    705
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Spurious decimal places (Excel 2000 - SR1)

    I've seen things like this happen before, with sums and other functions. One easy fix would be to use =ROUND(B1+A2,2) to add your previous running sum to the current amount. It still doesn't explain the problem, but it does make it's effects go away.

Posting Permissions

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