Excel is unusable for finance or accounting.

[baobob]

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?

***

 

[Ron Rosenfeld]

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?

By understanding how it works.

2) Why don't the dopes who write Excel LET you select either FP or
decimal storage?

***

Well, there are ways to do this. But since your Q's are rhetorical ...
--ron

 

[JE McGimpsey]

1) How can anyone use Excel for finance, accounting, or many
applications with dollars & cents, decimals, or simple fractions?

By being knowledgeable about how the application works. Which, btw, is
what anyone working with finance, accounting or other applications
should do with *ANY* software they use.

2) Why don't the dopes who write Excel LET you select either FP or
decimal storage?

Hmmm...if only your questions weren't rhetorical...

 

[Harlan Grove]

(e-mail address removed) wrote...

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 . . .

Simple fraction: 1/3

Try to represent it as a decimal fraction in a finite number of
decimal places. Same sort of thing happens with floating point math,
just using base-2 rather than base-10.

Two rhetorical Q's:

1) How can anyone use Excel for finance, accounting, or many
applications with dollars & cents, decimals, or simple fractions?

Obviously there are thousands if not millions who've figured out how
to do this. Too bad you're not one of them.

2) Why don't the dopes who write Excel LET you select either FP or
decimal storage?

Excel does provide this. Left as an exercise for you to figure out how/
where. Hint: time to read online help or figure out how to use Google
Groups search more effectively.

 

[Earl Kiosterud]

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...
-----------------------------------------------------------------------

 

[Harlan Grove]

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.

....

Wrong. Excel already provides a fixed-point alternative, meaning scaled
integer math, and it *IS* natively supported, though it's still done in
binary rather than decimal (there being no difference for integer math per
se, just differences in the text representations), and it's about as fast as
IEEE floating point, perhaps even faster. However, fixed point provides less
precision than floating point once you begin dividing or exponentiating
numbers. Addition, subtraction and multiplication of integers always
produces integers.

 

[ilia]

There's an option for "precision as displayed".