Re: Excel is unusable for finance or accounting.
- From: "Earl Kiosterud" <someone@xxxxxxxxxxx>
- Date: Wed, 22 Aug 2007 18:50:59 -0400
The problem isn't really in the floating-point representation of numbers, per se, or the
floating point math, to which it's often attributed. Floating point numbers still are
binary numbers. They just have a characteristic and a mantissa, giving them a wider range
of values, but less precision, for a given number of digits (binary bits, in the case of
binary numbers). Part of the problem is in the conversion from decimal to binary, then
back. It's inherent. Precision is lost. I won't go on about why, because there's been
much written about it. The other part is that in any number system, there are still some
mathematical results that can't be represented with ultimate precision, whether in decimal,
binary, or any other numbering system. Divide 1 by 3, and the result can't be represented
with perfect precision in either binary or decimal.
The underlying basis of all this is that ALL numbers are only approximations of a value. In
scientific work, it's well understood. Rounding is done routinely, and the precision of any
number is understood. But it confounds accountants, who deal in numbers that DO perfectly
represent values (until you do some math, like divide 1 by 3, and you're back in the world
of approximations again). Once you understand that the numbers, decimal or binary, are
approximations, it doesn't seem so bad. Having to deal with the limited precision is just
part of the work.
Could Exel do floating-point (or fixed-point, for that matter) in decimal? Yes. But they
don't. The processor can't do decimal math natively, so it'd have to be done in software,
slowing things down considerably.
If there were, as you ask, a way to get Excel to use decimal representations, and do the
math in decimal, it would make life a bit easier. There are some of us that hear your
cries, and echo them. But it appears it ain't gonna happen. But you still must deal with
the fact that the results are approximations.
In the mean time, use the rounding techniques that eliminate the errors (in typical
applications, anyway). I know this doesn't solve your problem, but I hope it makes it
easier to live with.
--
Earl Kiosterud
www.smokeylake.com
Note: Top-posting has been the norm here.
Some folks prefer bottom-posting.
But if you bottom-post to a reply that's
already top-posted, the thread gets messy.
When in Rome...
-----------------------------------------------------------------------
<baobob@xxxxxxxxxxx> wrote in message
news:1187810186.041551.258060@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
Re. Excel's inability to calculate decimal numbers precisely, due to
(pardon a layman's poss. wrong wording) using floating-point math to
handle simple fractions and decimals (see thread 'Glaring Excel calc
bug' in this group), Harlan Grove on July 2 provided the same answer
Paul Lynde once did on Hollywood Squares:
Peter Marshall: Now listen carefully, Paul. If you have one it's a
moose. If you have two, it's a....?
Lynde: It's a mess!
Two rhetorical Q's:
1) How can anyone use Excel for finance, accounting, or many
applications with dollars & cents, decimals, or simple fractions?
2) Why don't the dopes who write Excel LET you select either FP or
decimal storage?
***
.
- Follow-Ups:
- Re: Excel is unusable for finance or accounting.
- From: Harlan Grove
- Re: Excel is unusable for finance or accounting.
- References:
- Excel is unusable for finance or accounting.
- From: baobob
- Excel is unusable for finance or accounting.
- Prev by Date: Re: Using weekdays only in a schedule
- Next by Date: Re: Get a percentage based on multiple conditions
- Previous by thread: Re: Excel is unusable for finance or accounting.
- Next by thread: Re: Excel is unusable for finance or accounting.
- Index(es):
Relevant Pages
|
