Excel Rounding Error including Personal Budget Excel?
I'm using a relatively complex personal budget workbook
with Excel 2002 and seem to be having a rounding problem
with totals being calculated by Excel. Obviously, since
this is a budget workbook, everything is entered with 2
decimal places. However, after entering just over 600
transactions on one of the tabs, Excel is giving me a
result of 44.5100000000007 when everything has been
entered to two decimal places (I checked them to ensure
this is the true). In addition, Excel does not appear to
be consistent with its calculations. If I change one of
the inputs by just one penny, I get 44.5000000000005. It
sounds like an Excel patch may be required. Any guidance
you could provide would be appreciated.
It's a function of XL being run on a binary computer, not a bug. In
much the same way that 1/3 can't be fully represented in decimal (but
only approximated by 0.333333....), so most fractional value in binary
must be approximated rather than being represented exactly.
in the mean time, you can use the Round() function to reduce the small
errors, or Tools/Options/Calculation/Precision as desplayed to round
each value to it's displayed precision.