Results 1 to 4 of 4
  1. #1
    Lounger
    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.68434E-14 (i.e. to the power of minus 14).

    Why is this????? Shouldn't the answer to the calculation just be zero?

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

    Re: Calculation Error Query (XL 2002)

    If you perform the subtraction directly by using the formula =D11-D13, 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.

  3. #3
    Lounger
    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 Excel-based solutions for 4 year now, and have never come across this problem before.

    Thanks again.
    Regards,
    Steve

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 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 =D11-D13 instead of =SUM(D11-D13), the problem will not occur.

Posting Permissions

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