Excel can't do simple subtraction?!?!?!
EarlK
Excel converts the decimal numbers we type into cells into binary numbers.
This conversion can result in small errors because not all decimal numbers
have an exact binary equivalent, especially when the number of binary digits
is limited, as it is in Excel. (It uses the IEEE "floating point" number
representation, consisting of a binary number, called the fraction, and
another binary number, which is a multiplier. The number of binary digits
in the fraction determine the precision of the number, and the number of
binary digits in the multiplier determine the range of values that can be
represented). The result of a formula is converted back to decimal when
presented in a cell, which can also result in similar conversion errors.
Rounding the numbers can eliminate the errors that these small errors cause.
Formatting a number for a certain number of decimal digits automatically
rounds to that number of digits (1.2390000001 would round to 1.24 if
formatted for two digits to the right of the decimal). Then you can cause
those rounded numbers to be used by any formula referring to them by using
"Precision as displayed" in Tools, Options.
--
Regards from Virginia Beach,
EarlK
-------------------------------------------------------------
"Dayv Helfrich" <gdavidh...@comcast.net> wrote in message
news:7a6001c20344$974c61a0$39ef2ecf@TKMSFTNGXA08...
> I was creating a spreadsheet at work, when I noticed
> something strange...
>
> I noticed a couple places where Excel had a "$ (0.00)" in
> a calculated cell. (A negative zero?!) I expanded all of
> the cells out to 30 decimal places... some of the cells
> that had been showing the zeroes were actually very small
> decimals. If I were dividing anything in the calculation,
> I would understand this... my formula is only subtraction
> though.
>
> I'm attaching the spreadsheet for anyone interested in
> experimenting. The formula works fine through most of the
> spreadsheet. I highlighted the ones that gave strange
> results.
>
> Here's an example. . .
>
> In cell F111, I have the following formula:
> =E111-(C111-D111)
> The value of E111 is $73.890000000000000000000000000000
> The value of C111 is
> $2,540.490000000000000000000000000000
> The value of D111 is
> $2,466.600000000000000000000000000000
> The value of (C111-D111) should be
> $73.8900000000000000000000000000
> (simple subtraction)
> The value of E111-($73.890000000000000000000000000000)
> should be 0. (simple subtraction... subtract one number
> from itself and you get zero!)
> The result reported by Excel is:
> $0.000000000000127897692436818000
> WHERE is this number coming from??? If you are doing
> basic subtraction with numbers that terminate after 2
> decimal places (ie, the last 28 digits you see in each of
> these numbers are all zeros...) you can not possibly get
> the answer that Excel is reporting.
> I got the same result in Execl XP.
>
> Is this a bug? Or is there something strange in the way
> Excel performs its order of operations? Or can Excel just
> not subtract?
>
> Thanks!
> Dayv
>
Jerry W. Lewis
precision math issue, and therefore the basic nature of all numerical
software. All integer calculations will be exact, but floating point
calculations will usually be approximate due to finite precision.
A simple example is that 0.3333333 is not 1/3, but it is the best
approximation possible in 7-decimal places. If you use 0.3333333 to
approximate 1/3, then you should not be surprised if the result is only
approximate.
Almost all computer chips and computer software use binary rather than
decimal arithmetic because it is much more efficient. The only
additional surprise introduced by binary math is that most exact decimal
fractions are only approximate in binary. In particular, none of .49,
.6, or .89 have exact binary representations (just as 1/3 has no exact
decimal representation), so you should expect the answer of floating
point calculations to only be approximate since the inputs are only
approximate.
You got an answer that was correct to more than 16 digits
Log Relative Error = -LOG10(F111/C111)
which is not bad since IEEE double precision can only assure 15 correct
digits (see Help for "Excel specifications and limits" subsection
"Calculation specifications"
EarlK already suggested two of the three standard workarounds. The
third one is instead of testing for equality to zero, test whether the
number is less than some small amount. That is in effect what you are
doing visually by displaying 2 decimal places. Another approach if you
are never concerned about fractions of cents is to do all your
calculations as cents instead of dollars to avoid floating point
approximations.
When you calculate interest, using "Precision as displayed" will
probably not be satisfactory, since you will almost certainly be
interested in the fractional cents. Rounding away real information in
intermediate calculations will increase the degree of approximation in
the final result. This is not an issue if you are only adding and
subtracting numbers that do not have fractions of cents.
Rounding the final result to some meaningful precision has no downside
that I can see.
Jerry
EarlK
All very good points. Precision is an issue with any base number system
(binary, octal, decimal, etc). There are exactly one zillion unique values
that cannot be precisely represented in any number base. A zillion is
defined as the same as 1/0, which is "infinite." <g> Professor Higgins told
Colonel Pickering that he'd counted some 147 unique vowel sounds between "A"
and "Ah." Someone else could count more, and someone else more, etc.
In any real-world situation, where values are measured values, precision to
15 decimal places is one big mess of precision. A LOT. What bathroom
scale, accelerometer, voltmeter, etc. could measure to this accuracy anyway,
I wonder? So it's not only meaningless to represent such measured values to
15 decimal places, it implies a precision that is simply not there. So we
round to the number of decimal places to which the measurement is known to
be precise, though that gives pause to folks that have not come to terms
with this issue.
With regard to comparing against a small amount instead of zero, I was doing
that in earlier programming days, and felt a little guilty; it seemed
sloppy. But when one realizes that there is always imprecision, as you've
pointed out, it can be seen as quite acceptable.
Regards from Virginia Beach,
EarlK
-------------------------------------------------------------
"Jerry W. Lewis" <post_a_reply@no_e-mail.com> wrote in message
news:3CF0D49B.4030209@no_e-mail.com...