Excel's arithmetic is correct.
Most decimal fractions (including .8) cannot be represented exactly in
binary (much as 1/3 cannot be exactly represented in decimal). When you
use approximate inputs, it should be no surprise if the output is an
approximation.
Almost all computer software (including Excel) do binary math following
the IEEE standard for double precision binary representation:
94.8 gets approximated by 6670956948042547/70368744177664
14.8 gets approximated by 4165829655317709/281474976710656
Do the math, the exact answer to your approximate problem is
1/281474976710656, which Excel correctly reports.
In =80-(94.8-14.8)+0, which mackie suggested, if you do the math you
will see that 94.8-14.8 is not exactly 80, but the difference is beyond
the storage limits as outlined in the article that Frank referenced,
therefore the intermediate result gets rounded to 80 so that the final
result is zero.
The trailing +0 is needed to make the effects of these approximations
visible. When the final arithmetic operation involves subtraction of
two numbers that are equal to 15 decimal digits, then Excel will zero
the result even if the numbers are not equal beyond 15 decimal digits.
Since 15 decimal digits is the documented precision of Excel (see Help
for Specifications) the assumption is that differences beyond the 15th
decimal digit are probably junk from binary approximations. The
trailing +0 or wrapping the entire operation in parentheses makes the
subtraction no longer the final operation and thus bypasses this fuzz
factor.
How do you work with these approximations? First of all, it is not as
problematic as it may seem. You presumably are not worried about
having to approximate 1/3, Pi, e, etc. The approximations here are to
the same level of accuracy, you are just not used to thinking about
these numbers as needing approximations. The standard technique for
over half a century has been to not ask whether two floating point
numbers are equal, rather ask whether their difference is less than some
suitably small amount. A second equivalent approach would be to round
the results to an appropriate level. A third approach would be to do
something like SUMIF(data,">0")-SUMIF(data,"<0"). This third approach
will often (but not always) give zero when the answer to the
un-approximated problem is zero.
An easy way to determine the appropriate level of rounding is to use the
documented precision of 15 decimal digits. Your problem is then
80.0000000000000???
-94.8000000000000???
+14.8000000000000???
--------------------
0.0000000000000???
which Excel reports (15 decimal digits) as
0.0000000000000035527136788005
compared to the exact answer of
0.000000000000003552713678800500929355621337890625
Thus you could round the final result anywhere between 1 and 13 decimal
places.
Jerry
Hi folks,
In a cell if you were to put the following:
=80 - 94.8 + 14.8 + 0
You will not get zero as the result. Can anyone tell me why?
I ask because I am using the following calculation in Access (but I
get the same result there and Excel) to determine Inventory OnHand
Totals - [Receipts]-[Issuance]+[Restock]+[CycleCount]
Frequently there have been no adjustments made thus the 0 in the
calculation.
Any assistance would be most appreciated.
