Strange Calculation Error in Excel

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. 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/Compute...lgebra_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.

Jerry

"Jerry W. Lewis" <post_a_reply@no_e-mail.com> ???
news:4391A08A.4030909@no_e-mail.com ???...
> 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/Compute...lgebra_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(

Sub RoundAdd()
Dim myStr As String
Dim cel As Range
For Each cel In Selection
If cel.HasFormula = True Then
If Not cel.Formula Like "=ROUND(*" Then
myStr = Right(cel.Formula, Len(cel.Formula) - 1)
cel.Value = "=ROUND(" & myStr & "," & "2" & ")"
End If
End If
Next
End Sub


Gord Dibben Excel MVP

On Sat, 3 Dec 2005 23:20:10 +0800, "0-0 Wai Wai ^-^" <x@x.com> wrote:

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

Don't forget to use this on a copy of your worksheet.

Macros disable the "undo" function.


Gord

On Sat, 03 Dec 2005 08:23:57 -0800, Gord Dibben <gorddibbATshawDOTca@> wrote:

>Sub RoundAdd()
>Dim myStr As String
>Dim cel As Range
> For Each cel In Selection
> If cel.HasFormula = True Then
> If Not cel.Formula Like "=ROUND(*" Then
> myStr = Right(cel.Formula, Len(cel.Formula) - 1)
> cel.Value = "=ROUND(" & myStr & "," & "2" & ")"
> End If
> End If
> Next
>End Sub
>
>
>Gord Dibben Excel MVP
>
>On Sat, 3 Dec 2005 23:20:10 +0800, "0-0 Wai Wai ^-^" <x@x.com> wrote:
>
>>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.

0-0 Wai Wai ^-^ wrote:

> 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


I tend to agree. Some financial calculations are the only context I can
think of where I would be comfortable with Precision as Displayed.


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


Re-examine my decimal example. If you did a search for -1/3, would you
expect it to find -0.334, when -1/3 would calculate as -0.3333?

What happened in my decimal example, is that while the input numbers
were accurate to 4 figures, the subtraction canceled the first figure,
so the result was accurate to roughly 3 figures. Similarly with your
problem, the subtraction in =4.03-4.1 cancels 6 of the 53 bits used in
the binary representation of these numbers. Excel will not display more
than 15 meaningful digits (documented in Help for "Excel specifications
and limits"). Consequently the approximations involved in representing
4.03 and 4.1 are not apparent, but after canceling those 6 bits, the
result of these approximations is visible in the answer.

The closest you can approximate these numbers based on 53-bit accuracy
in the mantissa is
4.03000000000000024868995751603506505489349365234375
-4.0999999999999996447286321199499070644378662109375
-----------------------------------------------------
-0.06999999999999939603867460391484200954437255859375
which Excel correctly displays to 15 digits as
-0.0699999999999994
But the closest 53-bit approximation to 0.07 is
-0.070000000000000006661338147750939242541790008544921875
The difference between these two representations is
0.000000000000000610622663543836097232997417449951171875
which Excel correctly displays to 15 digits as
0.000000000000000610622663543836
It is this difference (analogous to the difference between -0.334 and
-0.3333 in my decimal example) that Excel is detecting when you try to
do COUNTIF(C4,-0.07)

You can see more than 15 digits of the binary representation of numbers
in Excel by using the VBA functions that I posted at
http://groups.google.com/group/micro...fb95785d1eaff5
But you can easily predict the magnitude of approximation without going
to such lengths. Just think in terms of the documented 15 figure limit.
Your problem is then
4.03000000000000???
-4.10000000000000???
--------------------
-0.07000000000000???
vs. the calculated result of
0.000000000000000610622663543836

Also, remember that this is not an Excel issue, rather it is a finite
mathematics issue compounded by approximations necessary in
decimal/binary conversions. Excel follows the IEEE standard for
internal representation of numbers, and so is no more or less accurate
than almost all general purpose software.

I know its a lot to take in at once, between this and your array formula
thread, but it will pay off in the long run.

Cheers,
Jerry

Hi Jerry,

> Extended precision packages like xlPrecision also do not solve the problem, they just push it farther out (though they do have their uses). <

This is true of some extended precision packages, but not true of
xlPrecision. xlPrecision never converts anything to binary. xlPrecision
does all arithmetic in base 10. Using xlPrecision results in no more
binary conversion errors than doing arithmetic in longhand (i.e.,
pencil and paper).

Also, I may be a little foggy on the definition, but I'm not sure that
it's quite accurate to refer to xlPrecision as "extended" precision.
xlPrecision is *arbitrary* precision in the sense that the underlying
algorithms have no maximum number of significant digits. xlPrecision's
maximum of 32,767 significant digits is simply the result of Excel's
limit of that many characters in a cell. I could easily extend that by
allowing array-entering into multiple cells, but I haven't done that
because I haven't heard of anyone wanting more than 32,767. If I were
to do so, the next limit I would reach is the largest text string
variable allowed, which would be a little over 2 billion significant
digits. Even that could be easily overcome by using arrays. Again, the
reason I haven't done it is because I don't think anyone would be
interested in that many significant digits.


Thanks,

Greg Lovern
http://PrecisionCalc.com
Eliminate Hidden Spreadsheet Errors

gregl@gregl.net wrote:

> Hi Jerry,
>
>>Extended precision packages like xlPrecision also do not solve the problem, they just push it farther out (though they do have their uses). <
>
> This is true of some extended precision packages, but not true of
> xlPrecision. xlPrecision never converts anything to binary.


Refer back to my decimal example. The basic issue is that some
calculations simply cannot be performed exactly in a numeric system that
has less than infinite precision. Decimal/binary conversions merely
extend that issue to numbers where we might not expect to see it.

Jerry