Results 1 to 4 of 4

20050401, 08:09 #1
 Join Date
 Feb 2003
 Location
 Bournemouth, Dorset, England
 Posts
 28
 Thanks
 0
 Thanked 0 Times in 0 Posts
Calculation Error Query (XL 2002)
I have a calculation that doesn't seem to be performing as it should.
Please see the attached workbook  don't open links when prompted (the reason it is linked is that if I flat paste the top value the calculation works properly, but this is only a small part of a very large spreadsheet so it is not possible for me to alter...)
Anyway, to be brief, it's just a simple calculation of the top value (D11) minus the bottom value (D13). Both values are in number format with lots of decimal places in view. Neither has any value other than zero after the first decimal place. However, when I perform the calculation (cell D21) which should result in zero, you will see that I am getting back some digits after the first 13 zeros. When the result is viewed in General format I then get the following : 5.68434E14 (i.e. to the power of minus 14).
Why is this????? Shouldn't the answer to the calculation just be zero?

20050401, 08:21 #2
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 30 Times in 30 Posts
Re: Calculation Error Query (XL 2002)
If you perform the subtraction directly by using the formula =D11D13, the result will be exactly 0.
When you use SUM, Excel calculates and stores intermediary results because you could be summing more than just one number. Small rounding errors can occur while doing this  Excel stores numbers internally in binary format (0s and 1s), and most decimal numbers cannot be represented exactly in binary format. Excel calculations are accurate to 15 significant digits. Your numbers have order of magnitude 10^2, the result of your formula has order of magnitude 10^14, i.e. a factor 10^16 smaller than the numbers you subtract. That is well within Excel's precision, so there is nothing to worry about.

20050401, 08:33 #3
 Join Date
 Feb 2003
 Location
 Bournemouth, Dorset, England
 Posts
 28
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Calculation Error Query (XL 2002)
Thanks for your quick response Hans.
I understand what you are saying, but I still don't fully understand why those erroneous values cannot be seen in the cell contents of D11.
It's not a major problem though, as I can just convert the calculation cell into Number format with 2 decimal places, and all works fine. Obviously bad design on my behalf  I should have done that originally when I created the spreadsheet!).
I'm hoping this will not occur too often though, as the spreadsheet this calculation appears on is used by 500 people across the company that I work for... :(
I've been creating Excelbased solutions for 4 year now, and have never come across this problem before.
Thanks again.
Regards,
Steve

20050401, 08:44 #4
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 30 Times in 30 Posts
Re: Calculation Error Query (XL 2002)
As I mentioned, Excel uses 15 significant digits. In D11, there are 3 digits before the decimal point, hence there are 12 significant digits after the decimal point. All decimals after that are not significant, and since they are not to be trusted, Excel displays them as 0.
If you use =D11D13 instead of =SUM(D11D13), the problem will not occur.