Results 1 to 5 of 5

20031029, 23:31 #1
 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 =a11b11+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 (=a11b11+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.

20031029, 23:54 #2
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 28 Times in 28 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.

20031029, 23:59 #3
 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.

20031030, 00:02 #4
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 28 Times in 28 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) < 1E10)
if A1 is the cell involved.

20031030, 02:32 #5
 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