Results 1 to 9 of 9

20020528, 09:41 #1
 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 2decimal 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

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

20020528, 12:17 #3
 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

20020528, 12:32 #4
 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 overdetailed 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

20020528, 16:15 #5
 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

20020528, 18:10 #6
 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

20020529, 06:27 #7
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 30 Times in 30 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.

20020529, 14:17 #8
 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

20020530, 10:45 #9
 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.