Re: scwewy answers that don't belong

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

On Dec 27, 3:39 pm, SteveDB1 <Steve...@xxxxxxxxxxxxxxxxxxxxxxxxx>
wrote:
I found a comparable article on that Excel bug you referenced,
dating back to May 10, 2005.

Actually, I was referring to a recent thread entitled "Is it a bug?",
9 Dec 2007, not this thread entitled "Excel bug?". But these threads
come up once a month, perhaps more often, or so it seems. It is a
FAQ.

And while the subject of the thread asks if this is an Excel bug, I
want to emphasize that the answer is always "no". Although Excel does
try to ameliorate the effect (e.g. the "optimization" introduced in
Excel 97 to reduce infinitessimal results to zero), for the most part
it is simply the nature of binary arithmetic in computers that use a
finite number of bits (namely 64) to store a "floating-point"
representation.


Hopefully MS will actually resolve this one day.

For the most part, it is not MS's problem to resolve. I do believe
that much of the problem would be avoided if Excel used decimal
arithmetic, as Visicalc did originally. But as Jerry Lewis is quick
to point out: that will not solve all the problems. Division and
multiplication by (some) fractional numbers will still cause problems,
depending on the precision of the decimal representation, as will the
representation of fractional numbers with infinitely repeating
series. Then there still remains the issue of representing numbers
with a finite number of digits, which the IEEE 754 decimal floating-
point representation proposes.

In fact, it is interesting that a (finite) decimal representation
could introduce new anomalies. For example, if A1 is 1/3 and A2 is
3*A1, binary computer arithmetic returns exactly 1 in A2, as you would
like. This is not a gimmick of Excel; the same thing is true in VBA.
It has to do with how the binary implementation rounds the longer
internal result to 64 bits. (The exact binary representation before
internal rounding is actually infinitessimally less than 1.)

But an "exact" decimal representation might return 0.9...9. It
depends on the implementation. (I suspect an IEEE 754 decimal
floating-point implementation would still yield exactly 1. Intel does
provide a software library with one implementation of the proposed
standard. I don't have access to it.)


It almost seems that the calculations taking place are getting skewed in the
background, and then returning an erronous answer. I.e., a computer issue.

I take issue with the phrase "erroneous answer". It is a question of
finite v. infinite representation. Assuming that "infinite
representation" is unrealistic, there will also be numerical
"error" (difference) in any computation, whether it be by hand or by
computer. There is an entire branch of mathematics devoted to
studying this called Numerical Analysis. It predates the advent of
computers.

The issue is made more mysterious with the advent of computers because
human calculation usually try to "reduce terms" as much as possible
before it becomes necessary to actually compute a number. And because
humans do use a form of finite decimal representation that tends to
avoid computational error in some simple cases. It is my contention
that if computers mimicked the same finite decimal representation, no
one would squawk about the "error" in computation because it would be
the same as the error in human computation of the same problem -- or
at least the explanation would easily be understood in those terms.
.

Relevant Pages

  • Re: scwewy answers that dont belong
    ... I'm fully cognizant that computers don't make mistakes, ... not this thread entitled "Excel bug?". ... depending on the precision of the decimal representation, ... it is interesting that a decimal representation ...
    (microsoft.public.excel.worksheet.functions)
  • Re: Strange Calculation Error in Excel (2)
    ... If, say, computers can only store 0.06999999...9994 for 0.07, so when I type 0.07, computers should actually treat it as 0.069999999...9994. ... Excel will not display more ... Consequently the approximations involved in representing ... You can see more than 15 digits of the binary representation of numbers ...
    (microsoft.public.excel.misc)
  • Re: The Most Profound Distillation of the Feedack from Modern Cyptography.
    ... Therefore all numbers that computers use are ... transparent origins that will keep on surfacing in the ciphertext, ... representation as a kind of temporary surrogate representation of the ... My solution is the analogue representation of the same numbers but on ...
    (sci.crypt)
  • Re: God Proven to Exist According to Mainline Physics
    ... deny or remain agnostic about humans and computers ... But the equivalence lies solely in the representation. ... The reason why we represent information processing systems as IPSs (or ... neighbouring circuits, or there's quantum tunneling between circuits ...
    (talk.origins)
  • Re: God Proven to Exist According to Mainline Physics
    ... deny or remain agnostic about humans and computers ... But the equivalence lies solely in the representation. ... as Turing machines, or as Algorithmic Information Systems, etc.) is ... neighbouring circuits, or there's quantum tunneling between circuits ...
    (talk.origins)