Results 1 to 5 of 5
  1. #1
    New Lounger
    Join Date
    Oct 2003
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts

    When does 0 not equal 0? (Excel 2002 + Excel 95)

    One of my staff had an interesting problem... She had several columns of numbers and was running a simple formula on them then taking the result of that formula and running it through a conditional format if the number was = 0. On some lines the conditional formatting worked fine and on others it did not. For example, one of the lines that didn't work looked like this:

    80.79 76.75 (4.04) 0 =a11-b11+c11+d11

    All of the cells were formatted as numeric and all of them had been hand keyed so there wasn't the problem of a formula inside a formula.

    Just for fun I thought to expand out the decimal places and sure enough, the result of the formula was NOT 0. It was 0.00000000000000621724893790088
    so no wonder the conditional formatting was messed up.

    It turns out that if I exclude the cell that contains the 0 from the formula (=a11-b11+c11) it works fine. It's only when I attempt to add the cell containing a 0 that it comes up with the huge decimal. Autosum also showed it in scientific notation and it didn't seem to matter if I was adding negative numbers or subtracting positive ones.

    So what do you think? Is this a math bug in Excel or what? Or am I just old fashioned to think that if you add 0 to 0 the result should actually be 0 instead of 0.00000000000000621724893790088?

    For the record, her machine is a Pentium with Office 95 and mine is a P4 with Office XP. It does the same thing on both.

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

    Re: When does 0 not equal 0? (Excel 2002 + Excel 95)

    Human beings use the decimal system to write numbers, computers use the binary system (consisting of 0's and 1's). Some "nice" fractions in decimal notation also look "nice" in binary notation, for example 0.5 (decimal) = 0.1 (binary), but many don't, for example 0.2 (decimal) = 0.001100110011001100110011... (binary). Of course, a computer can only store numbers with a finite precision, so small rounding errors occur. Excel stores numbers with about 15 significant digits (decimal). When you add or subtract a few numbers, these rounding errors can accumulate. Adding or removing numbers from a calculation may change the way Excel performs the calculation internally, so even if inserting a 0 into a calculation shouldn't influence the result from a mathematical viewpoint, it can make a difference in the finite precision Excel uses.
    The moral of this story is that you should never attach any significance to rounding errors. In your example, you are adding/subtracting numbers of the order of magnitude 10^1 (10 to the power 1), and the result is a number of the order 10^-15 (10 to the power -15). That is 10^-16 times as small as the numbers being added/subtracted, so well within the accuracy of 15 significant digits.

  3. #3
    New Lounger
    Join Date
    Oct 2003
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: When does 0 not equal 0? (Excel 2002 + Excel 95)

    Just for fun I fired up Star Office 5.2 and it works exactly as expected. This appears to be unique in the way Excel does calculations.

    I do accept that it's a rounding error, what seems erroneous to me is that the rounding error is making the Conditional Formatting fail to operate properly.

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

    Re: When does 0 not equal 0? (Excel 2002 + Excel 95)

    Clearly, Star Office has a different way of dissecting and assembling the parts of a calculation. If you want the conditional formatting to work, you can use a formula instead of a value as condition, for example

    =(ABS(A1) < 1E-10)

    if A1 is the cell involved.

  5. #5
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: When does 0 not equal 0? (Excel 2002 + Excel 95)

    or something like:
    =Round(a1,5) = 0

    Which might seem a little clearer later on when you look and wonder what the formula is doing.

    Steve

Posting Permissions

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