INT defect: Please try this on 2007 for me

[Jerry W. Lewis]

Right. And that works because the binary representation of a constant
rounded to 15 sig digits is the same as the binary representation of the
constant by definition, because Excel limits constants to 15 sig digits. So
there is no problem.

OK, then explain ROUNDDOWN(40000.846-2^-37,3)

It is generally considered good form to produce a counter-example before
dismissing out of hand an explanation for an issue that you have raised. I
provided an explanation under which your issue would be by intentional
design. Where is your counter-example?

You insist that ROUNDDOWN(x,3) rounds based on the exact binary
representation, but that cannot simultaneously explain the examples that I
have provided to back up my explanation. Can you find ANY example where the
Excel functions
ROUNDDOWN(x,n), ROUNDUP(x,n), ROUND(x,n), INT(x), etc behaves differently
than if the argument were VALUE(x&"")? I reached this explanation a number
of years ago and have yet to see anything that contradicts it.

My explanation not only matches the results of every example I have seen, it
makes sense in the context of the MS track record of trying to gloss over the
impact of binary calculations that would be surprising to someone who has not
thought about binary vs decimal issues. Unfortunately the only way to gloss
that over is to warp the arithmetic, which then produces far more difficult
to understand issues than what they were trying to avoid. I agree that there
is a problem, but the problem seems to be with the general "optimization" of
http://support.microsoft.com/kb/78113
which MS did intentionally (and has shown no signs of regretting), not with
your INT function issue, which appears to be just one example of the
implications of that "optimization".

Jerry

 

[JoeU2004]

[Sorry for the late response. I got busy.]

Most real-world spreadsheets contain a chain of calculations
where the effect of the floating-point calculations makes an
absolute comparison of the low-order bits such as you are
attempting a pointless exercise.

I have answered many dozens of queries posted in these forums involving
real-world situations where the root cause is related to the side-effects of
binary floating point representation and calculations.

My examples are not intended to demonstrate the real-world situation per se,
but the root cause. Often they are a distillation of the real-world
calculations. Sometimes I choose my parameters to demonstrate the boundary
conditions of those calculations. But in all cases, the results my examples
can arise in normal Excel calculations.

Many people have expressed appreciation of the detailed examples that I
provide because it helps them to visualize an esoteric concept that is
otherwise beyond their scope of expertise.

As you have demonstrated, if you need such accuracy in
the low-order bits you should not be using Excel.

It is not that I (and the users that I represent) "need" such accuracy. It
is the fact that Excel calculations employ or result in such accuracy,
contrary to misguided conventional beliefs, demonstrated by your comments,
and misleading MS documentation. Consequently, users stumble into
situations where that accuracy leads to unexpected results.

To be sure, the solution is for users to ensure that the result, at least,
has no more accuracy than they expect, usually by the prolific, but prudent
use of ROUND.

but I think (without going into the gory details) that
Excel is working as designed in your example.

You are entitled to your opinion.

But even if INT is working "as designed" (i.e. a conscious choice by the
implementer), the rhetorical question is: is the design correct?

Again, that is a matter of opinion.

I find it difficult to believe that any reasonable person would not be
surprised by A1-INT(A1) returning a negative result for positive A1, since
that cannot be explained by knowledge of the binary representation alone.

Just as I would find it difficult to believe that any reasonable person
would not be surprised by the fact that the constant 40000.848 is displayed
as 40000.847999999 when formatted to 15 significant digits. Again, that
cannot be explained by knowledge of the binary representation alone.

But I guess you would insist the latter is not a problem since "Excel is
working as designed" ;-).


----- original message -----

 

[JoeU2004]

[Sorry about the late response. I got busy.]

You insist that ROUNDDOWN(x,3) rounds based on the exact binary
representation, but that cannot simultaneously explain the examples
that I have provided to back up my explanation. Can you find ANY
example where the Excel functions ROUNDDOWN(x,n), ROUNDUP(x,n),
ROUND(x,n), INT(x), etc behaves differently than if the argument were
VALUE(x&"")? I reached this explanation a number of years ago and
have yet to see anything that contradicts it.

I don't know of any examples that contradict your conclusion.

I did not provide any such examples because I was __agreeing__ with you.
At least, that was my intention. My intention was to describe what your
VALUE(A1&"") does, not to offer an alternative explanation.

But aha! I think I see the subtle difference in our wordings.

When A1 contains a number, VALUE(A1&"") converts A1 as Excel would do to
display the numeric cell value, namely up to 15 significant digits, rounding
the 16th significant digit.

(I am not saying that VALUE() per se does that. It is actually the
conversion of numeric A1 to text that causes the rounding.)

I was trying to say the same thing when I said "rounding the binary to 15
significant digits". I took "as Excel would do to display the numeric
cell value" for granted because in the form VALUE(A1&""), we are relying on
Excel to do the conversion, and I know of no way that Excel performs such
conversions other than "as [it] would to display the numeric cell value".

But I had not thought of other ways of performing the conversion, e.g. a UDF
that calls CStr.

So I agree: I erred in omitting "as Excel would do to display the numeric
cell value". That is more precise because it covers any defective
Excel conversions, e.g. the one described in
http://support.microsoft.com/kb/161234.

OK, then explain ROUNDDOWN(40000.846-2^-37,3)

If we enter =40000.846-2^-37 into A1, the exact internal representation is
40000.8459999999,9045394361019134521484375. In that case, VALUE(A1&"")
results in exactly 40000.8459999999,977299012243747711181640625, which is
the exact internal representation of 40000.846. So ROUNDDOWN returns the
binary representation of 40000.846 instead of 40000.845.

In contrast, if we enter =40000.846-7*2^-37 into A1, the exact internal
representation is 40000.8459999999,46798197925090789794921875. In that
case, VALUE(A1&"") results in exactly 40000.8459999999,03142452239990234375,
which is the exact internal representation of 40000.8459999999. So
ROUNDDOWN returns the binary representation of 40000.845, namely
40000.8450000000,0116415321826934814453125.

Based on those examples (and others that I have mentioned in this thread),
when A1 is numeric, VALUE(A1&"") appears to be rounding the internal binary
representation to 15 significant digits, no matter how you choose to
interpret that description.

(Again, it is actually the conversion of numeric A1 to text that causes the
rounding, not VALUE per se.)

But that simple description does not necessarily cover the defect in KB
161234, unless you realize that I meant to say "as Excel would do to display
the numeric cell value".

For example, if we enter 40000.848 into A1, the exact internal
representation is 40000.8479999999,9813735485076904296875. By visual
inspection and a literal interpretation of my description, we might expect
VALUE(A1&"") to result in the binary representation of 40000.848 again.
Thus, we would expect ROUNDDOWN(40000.848,3) to also return the binary
representation of 40000.848.

But VALUE(A1&"") actually results in
40000.8479999999,035499058663845062255859375, the binary representation of
40000.8479999999, because that is how the defective Excel display conversion
algorithm presents 40000.848 (in Excel 2003). So ROUNDDOWN(40000.848,3)
results in the binary representation of 40000.847, namely
40000.8470000000,015716068446636199951171875 because of the defective
display conversion.

In conclusion, I believe we are in violent agreement -- at least you are --
to wit: the Excel 2003 round and truncate functions [*] treat their
argument as VALUE(A1&""). That is, they round the argument to 15
significant digits, as Excel would do to display the numeric cell value,
before doing the appropriate round or truncate operation.

I believe the horse is turning over in its grave .


-----
Endnotes

[*] I have not bothered to check Excel 2003 ATP round/truncate functions,
e.g. MROUND.

All comments and examples are for Excel 2003 11.5612.5606, part of MS
Office Sm Busn Ed 2003 on MS Win XP SP3. They may or may not apply to other
revisions of Excel 2003 and other versions of Excel.

All other disclaimers apply, implied and explicit. Void where prohibited by
law. "Don't tread on me". "Sell no wine before its time".


----- original message -----

 

[Charles Williams]

We both agree that Excel calculations can produce results when comparing
calculated numbers that surprise many people.

Maybe where we differ is in our response to the surprised people.

I find it more helpful, rather than try to find or predict the exact
accuracy limits of a given calculation or to provide a detailed explanation
at the bit level, to just explain that because Excel calculates using
floating-point binary and presents results in decimal it is not wise to rely
on any particular degree of absolute accuracy in comparisons, and that you
should instead use a meaningful comparison tolerance.

regards
Charles
___________________________________
The Excel Calculation Site
http://www.decisionmodels.com

 

[Jerry W. Lewis]

Which suggests that MS is still wedded to their unfortunate "optimization"
http://support.microsoft.com/kb/78113
despite the confusion that it causes.

Jerry

 

[Charles Williams]

Jerry,

I don't think that this behaviour is caused by that particular MSoft
"optimisation": Excel 5 behaves exactly the same way.

Interestingly OO Calc gives these very slightly different but similar
results
123456789-0.0000004 the INT test gives 0
123456789-0.0000005 the INT test gives -0.0000006066395
123456789-0.0000006 the INT test gives 0.9999994039536

I guess if anyone really wants to see whats going on here they could debug
OOCalc.

regards
Charles
___________________________________
The Excel Calculation Site
http://www.decisionmodels.com

 

[Charles Williams]

I think the comment about Excel using its internal binary to decimal
conversion routine in INT before truncation is probably correct.

If you make Excel display lots of decimals this UDF (.Text gets the
formatted value) gives exactly the same results as the Excel INT function.

Public Function VBAINT(theCell As Range) As Double
VBAINT = Int(theCell.Text)
End Function

BTW Gnumeric gives slightly different answers but will also show negatives
for the INT test.


Charles
___________________________________
The Excel Calculation Site
http://www.decisionmodels.com