Re: Strange Calculation Error in Excel (2)



"Jerry W. Lewis" <post_a_reply@xxxxxxxxxxxxx> ???
news:4391A08A.4030909@xxxxxxxxxxxxx ???...
> 0-0 Wai Wai ^-^ wrote:
>
> > It doesn't really matter how the number is displayed after, say, 15 decimal
> > points.
> > But what I want is it can still be calculated without being affected by this
> > minor mistake.
>
>
> Calling it a "mistake" suggests that you still do not understand. It is
> an inevitable consequence of finite precision mathematics.

Thanks for your explanation.
I did know a bit after the first reply.
At that time, I searched for information about this problem.

Just a thought to me. Since it is an inevitable consequence is notihng to do to
say whether it is not a mistake. Humans always make mistakes. It is an
inevitable consequence in our life. But does that mean they are no longer
mistakes then since they are inevitable?
Inevitablity is nothing to do with classifying a mistake.

Anyway, this correspondence is just a casual one. Thus not every word is
carefully thought out before written. Maybe "this sort of problem" should not be
called mistake since it seems I am blaming my computer without any appreciation
of its limitation. Maybe "error", or "natural beast" is a better name for "this
problem".


> Suppose you
> were doing decimal math with 4-figure precision. Then (4+1/3)-(4+2/3)
> would be
> 4.333-4.667 = -0.334
> There is no mistake, but the result is numerically different from the
> representation of 1/3 = 0.3333 in this system.
>
> You are presumably aware that numbers like 1/3, 1/7, 1/11, etc. cannot
> be exactly represented in decimal, and so you are not surprised when
> numbers like these have to be approximated. The only additional
> surprise here is that numbers like 1/5 are also non-terminating binary
> fractions, with the result that most finite decimal fractions (including
> .03, .07, .1, .15, and .97) can only be approximated. When you do math
> with approximate inputs, you should not be surprised when the result is
> also an approximation. It is not an "error", "mistake", "imperfect
> conversion", etc. it is just the nature of the beast.
>
> Converting to BCD as joeu2004 suggested would not eliminate the problem,
> as my decimal example illustrated. It would just confine the problem
> (finite precision approximation to numbers that can only be exactly
> represented in infinite precision) to numbers where we more readily
> recognize what has happened. BCD is rarely done in computers, because
> it is relatively wasteful and slow, which seems a steep price to pay for
> a "solution" that doesn't fully solve the problem.
>
> Extended precision packages like xlPrecision also do not solve the
> problem, they just push it farther out (though they do have their uses).
> The only way to completely solve the problem is to do symbolic math
>
http://en.wikipedia.org/wiki/Computer_algebra_system#List_of_computer_algebra_systems
>
> But the performance penalty from that option would be totally
> unacceptable for large spreadsheets.
>
>
> > Just like the countif function. It can't calculate well due to the small
> > difference of 0.00....005
> > Any workaround is appreciated.
>
>
> If you are unwilling to standardize the approximations (using ROUND() on
> the calculations or setting the Precision as Displayed option), then you
> need to do comparisons that are robust to approximations. Examples
> would include IF(ROUND(C4,2)=0.07,... or IF(ABS(C4-0.07)<0.005,...
> For summarizing a range, this would generally require array formulas.

I'm willing to use round(), but there are tons of rewriting.
It seems to be impossible to rewrite all of them by human.
It would be great if you could suggest a method which can rewrite 1000 formulas
automatically.

As to "Precision as Displayed", it is a bad idea since I will either sacrifice
precision or force me to display 10-decimal-point for every figure (clumsy
looking :(

Anyway, I just wonder why countif won't work under its "binary-to-decimal"
problem.
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 (since it can't
store 0.07 precisely).

Hmm... I know I am probably asking stupid questions.
But when I type countif(A1,0.07), it won't count it.
What does it imply?
Doesn't it mean computers can still store 0.07?
To computers, 0.07 or 10-9.93 should mean the same as computers, ie
0.06999...9994. But from the result, it seems computer read the first one as
0.07, the second as 0.06999...9994.
OK, I'm going idiotic. X(


.