Hi,

Curious

A2 =123456789-0.0000004 B2 =A2-123456789=0 =False
A3 =(12346789-0.0000004) B3 =A3-123456789=0 = False
A4 =INT(123456789-0.0000004) B4 =A4-123456789=0 =True

But then

=A2=A3 returns false
=A3=A4 returns false
=A2=A4 returns True

Mike

"JoeU2004" wrote:

"Mike H" wrote:
In E2007 both
=INT(123456789 - 0.0000004)
and
=123456789 - 0.0000004
returns 123456789

Normal Excel formatting is not sufficient to really know what 123456789 -
0.0000004 really is.

But if that formula is in A2, what does =A2-123456789=0 return?

Oh, and try writing =(12346789 - 0.0000004). Note the extra parentheses.
At least in Excel 2003, that changes (avoids) a heuristic that Excel uses to
"correct" results. It does not apply in Excel 2003; but Excel 2007 is a
different animal.

(Which also means that some of my tricks to avoid Excel "intelligence" will
not work in Excel 2007. Sigh. I really should bite the bullet and install
Excel 2007 myself.)


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

"Mike H" wrote in message
...
Hi,

In E2007 both

=INT(123456789 - 0.0000004)
and
=123456789 - 0.0000004

returns 123456789

Mike

as doe


Mike



"JoeU2004" wrote:

"Jerry W. Lewis" wrote:
Part of that "optimization" appears to be that bit stripping functions
(ROUND, ROUNDDOWN, ROUNDUP, INT, etc appear to be based
on VALUE(x&"") instead of on x itself.

Sounds a lot like my speculation that their algorithm is "effectively,
albeit perhaps unintentionally, rounding its argument to 15 significant
digits before truncating to an integer".

But clearly that is incorrect for INT, for example. If A1 is positive,
A1-INT(A1) cannot be negative, as it is for the example that I gave.

That is why I label this behavior as a defect.

I simply want someone to try it in Excel 2007 and let me know the
results.
(Excel 2010 would be a bonus.)


You could probably use a UDF to accomplish your purpose.

Y'think? How'bout the myInt function that I included in my posting?
(Klunk!)

I have no other purpose to accomplish than to learn factually, not by
speculation, how my example behaves in Excel 2007 (and Excel 2010, if
someone has it).


consider that =ROUNDDOWN(40000.848,3) returns 40000.847
in Excel 2003 and 40000.848 in Excel 2007

I do not have a problem with ROUNDDOWN(40000.848,3) returning 40000.847.

40000.848 is exactly 40000.8479999999,9813735485076904296875 internally,
and
ROUNDDOWN(40000.848,3) is exactly
40000.8470000000,015716068446636199951171875 internally. Since 40000.848
=
ROUNDDOWN(40000.848,3), there is no mathematical inconsistency.

This is simply the sort of unfortunate anomaly of binary floating point
representation that we (you and I) explain to people all the time.

There might be no harm done if ROUNDDOWN(40000.848,3) were changed so
that
it returns (the binary representation of) 40000.848. It would still be
the
case that 40000.848 = ROUNDDOWN(40000.848,3).

However, that depends on how they accomplished that, if they did it at
all.
(You are merely speculating.) There may be other examples that now would
break.


2007 fixed the display bug that is much bigger than is acknowledged at
http://support.microsoft.com/kb/161234

KB 161234 is a different animal. That deals effectively with
ROUND(40000.848,3), not ROUNDDOWN. Certainly
40000.8479999999,9813735485076904296875 should ROUND to 40000.848 for 3
dp.

So it possible that MS fixed KB 161234 without having the impact on
ROUNDDOWN that you speculate.


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

"Jerry W. Lewis" wrote in message
...
Unless MS has finally backed off their unfortunate "optimization"
http://support.microsoft.com/kb/78113
that warps Excel's math at the edges to try to hide the binary
underpinnings, I would expect the result to be the same in 2007, 2010,
...

Part of that "optimization" appears to be that bit stripping functions
(ROUND, ROUNDDOWN, ROUNDUP, INT, etc appear to be based on VALUE(x&"")
instead of on x itself. As evidence of this conclusion, consider that
=ROUNDDOWN(40000.848,3)
returns 40000.847 in Excel 2003 and 40000.848 in Excel 2007. Someone
please
verify in 2007; the 2007 part is a prediction based on my observation
[from a
time when I had access to 2007] that 2007 fixed the display bug that is
much
bigger than is acknowledged at
http://support.microsoft.com/kb/161234

You could probably use a UDF to accomplish your purpose. VBA was never
subject to that display bug, and I don't think that its bit stripping
functions involve an intermediate string conversion.

Jerry

"JoeU2004" wrote:

Would someone who has Excel 2007 running on an Intel-compatible
computer
(i.e. not a Mac) please try the examples below and post your results
here?

I posted this inquiry to an Excel 2010 blog. Not sure if/when I'll
hear
back from them.

(Extra credit: If you have Excel 2010 running on an Intel-compatible
computer, feel to try these examples and post your results here, too.)


Has the following Excel 2003 problem been fixed in 2010 (or 2007)?

INT(123456789 - 0.0000004) returns 123456789 instead of 123456788.

This causes a problem in formulas like the following: if A1 is
=123456789-0.0000004, =A1-INT(A1) is negative unexpectedly,
about -4.917E-07
when formatted as General.

In contrast, INT(123456789 - 0.0000005) returns 123456788 as expected,
and
myInt(123456789 - 0.0000004) returns 123456788, where myInt() is the
following UDF:

Function myInt(x as Double) as Double
myInt = Int(x)
End Function

Note that 123456789 - 0.0000004 is represented internally as about
123456788.999999,598, whereas 123456789 - 0.0000005 is about
123456788.999999,493 internally. (The comma demarcates the first 15
significant digits.)

So I suspect that the Excel INT algorithm is effectively, albeit
perhaps
unintentionally, rounding its argument to 15 significant digits before
truncating to an integer. It shouldn't.

Indeed, the largest expression involving 123456789 that returns an
incorrect
INT value is 123456789 - 33*2^-26, which is represented internally as
about
123456788.999999,508, whereas 123456789 - 34*2^-26 is about
123456789.999999,493 internally.

As you might imagine, the problem is not limited to 123456789 -
0.0000004.
And the problem will not appear with some combinations that you might
think
are similar, e.g. 100000000 - 0.0000004.

You need to look at the exact conversion of the internal binary
representation -- that is, beyond the first 15 significant digits --
to
determine whether or not to expect a problem. Most people cannot; I
can.