INT defect: Please try this on 2007 for me

[JoeU2004]

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.

 

[Charles Williams]

All numbers in Excel are IEEE Binary floating point.
This approximates to 15 significant decimal digits, as you have found.

This is the way it is designed to work, and is as outlined in Excel Help
etc.

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

 

[Jerry W. Lewis]

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]

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

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

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.

 

[Mike H]

Hi,

In E2007 both

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

returns 123456789

Mike

as doe


Mike

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

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

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.

 

[JoeU2004]

All numbers in Excel are IEEE Binary floating point.

Of course. Whadaya think I meant when I wrote, "You need to look at the
exact conversion of the internal binary representation -- that is, beyond
the first 15 significant digits"?

And wheredaya think I'm getting all those extra digits when I wrote that
123456789 - 0.0000004 is "about 123456788.999999,598"?

(It is exactly 123456788.999999,59766864776611328125. Or if you prefer
binary, &h419D6F34,53FFFFE5, which is a stylistic way of writing
0x419D6F3453FFFFE5 in C.)

This approximates to 15 significant decimal digits, as
you have found. This is the way it is designed to work

Sorry, but you are wrong. Reason it out for yourself.

If you were right, how could I subtract 0.0000004 from 123456789 in the
first place? And how could subtracting 0.0000005 have different results?

(Hint: Reread my original posting. I answer those questions.)

And if A1 is positive, A1-INT(A1) should never return a negative number.
Zero, maybe; but not negative.

Finally, for your edification, try the following experiment.

A1: 12345789
A2: =A1 + 2^-26
A3: =A1-A2=0

Format A1 and A2 to 15 significant digits (e.g. Scientific with 14 dp).
Note that A1 and A2 __appear__ to be the same. But A3 being FALSE proves
they are not.

Even though Excel formatting is limited to 15 significant digits (as is data
entry), arithmetic is performed to the full precision of 64-bit floating
point, which is more than 15 significant digits.

(Actually, pairwise operations are performed to the precision of 80-bit
floating point, then rounded to 64-bit floating point.)

as outlined in Excel Help etc.

I'm afraid that MS tech writers tend to over-simplify technical
explanations, and they often get it totally wrong.

For example, http://support.microsoft.com/kb/78113 states: "although Excel
can store numbers from 1.79769313486232E308 to 2.2250738585072E-308, it can
only do so within 15 digits of precision".

That is flatly incorrect, as it relates to results from arithmetic
operations, as I demonstrate above.

(And arguably, if the sentence above were intended to refer to storing
constants, not results of calculations, the tech writer is still wrong
because constants have a more limited range, at least in Excel 2003.)


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

 

[Ron Rosenfeld]

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.

Here is what I get in Excel 2007 SP2 (results formatted as General)

=INT(123456789 - 0.0000004) --> 123456789

=A1-INT(A1) --> 0

--ron

 

[Ron Rosenfeld]

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;

This is very interesting.

When I type the value 40000.848 into a cell in Excel 2007 SP2, it changes to
40000.8479999999 (in the formula bar)!!!

And your rounddown formula cannot be entered as written! The constant also
gets changed (even with a copy/paste) and then formula becomes:

=ROUNDDOWN(40000.8479999999,3) which gives the result of 40000.847


--ron

 

[Niek Otten]

That 's strange:

For =A1-INT(A1) I get -4.02331E-07

Excel 2007 SP2

 

[Jerry W. Lewis]

Sorry if my point was not clear, ROUNDDOWN(40000.848,3) matching your
expectation is an exception, not the rule.
=ROUNDDOWN(40000.846,3)
is not subject to the display bug, has a binary value slightly less than
40000.846 and returns 40000.846 as I my description predicted, not 40000.845,
as you would seem to expect.

Jerry

:
....

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.

....

 

[JoeU2004]

Here is what I get in Excel 2007 SP2 (results formatted as General)
=INT(123456789 - 0.0000004) --> 123456789
=A1-INT(A1) --> 0

Given the first result, the second result is a surprise.

I wonder if Excel 2007 changed the behavior of General format.

Try formatting at least the A1-INT(A1) cell lwith Scientific format with 14
decimal places.

Alternatively, Excel 2007 might have changed the way 123456789 - 0.0000004
behaves (yikes!). Unfortunately, there is no way to see that with normal
formatting.

If you would be willing to use my macros for formatting beyond 15 sig
digits, send me email at joeu2004 "at" hotmail.com.


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

 

[JoeU2004]

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

Hi,

In E2007 both

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

returns 123456789

Mike

as doe


Mike

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

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

:

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.

 

[Mike H]

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

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

Hi,

In E2007 both

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

returns 123456789

Mike

as doe


Mike

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

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

:

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.

 

[JoeU2004]

=ROUNDDOWN(40000.846,3)
is not subject to the display bug, has a binary value slightly less than
40000.846 and returns 40000.846 as I my description predicted, not
40000.845, as you would seem to expect.

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.

The problem arises because Excel does not limit the binary representation of
the result of calculations to 15 sig digits. So rounding the binary
representation to 15 sig digits does not always work.

That is why rounding 123456789 - 0.0000004 to 15 sig digits does not work.
Instead of resulting in exactly the same binary representation, the result
is the representation of a different number, namely exactly 123456789 in
this case. That violates the Principal of Least Surprise for the INT
function (el at) because it is larger than the original argument.

That's the defect: the algorithm of rounding to 15 sig digits first does
not necessarily work unless the numbers had been rounded to 15 sig digits in
the first place. For all other numbers, it will "sometimes" work. Thus,
the algorithm is numerically unsound. And that was my original point.

I was not seeking an explanation of the misbehavior. I had already
speculated the root cause.

It is up to MS whether or not to consider it defect. But Excel 2010 would
be an auspicious time for MS to address it, if MS hasn't already in Excel
2007, since Excel 2010 attempts to address other numerically unsound
algorithms.

That is why I wanted to know the behavior in Excel 2007. No sense in
ringing the bell if the problem has been fixed already.

I conclude it has not, based on Niek's response.


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

 

[JoeU2004]

For =A1-INT(A1) I get -4.02331E-07
Excel 2007 SP2

Thanks. I consider that is dispositive.


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

 

[Ron Rosenfeld]

Given the first result, the second result is a surprise.

I wonder if Excel 2007 changed the behavior of General format.

Try formatting at least the A1-INT(A1) cell lwith Scientific format with 14
decimal places.

Alternatively, Excel 2007 might have changed the way 123456789 - 0.0000004
behaves (yikes!). Unfortunately, there is no way to see that with normal
formatting.

If you would be willing to use my macros for formatting beyond 15 sig
digits, send me email at joeu2004 "at" hotmail.com.

No need. I see what I missed.

Actually, the formula

=INT(123456789 - 0.0000004) was *IN* A1 in my first response to you.

So =A1-INT(A1) --> 0

But if A1 contains:

=123456789-0.0000004

Then

=A1-INT(A1) --> -4.02331E-07


--ron

 

[JoeU2004]

What exactly do you find "curious"?

Also, double-check for typos, both in your posting and in your worksheet.
Both =A2=A3 and =A3=A4 return TRUE in Excel 2003.


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

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

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

Hi,

In E2007 both

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

returns 123456789

Mike

as doe


Mike



:

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

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

:

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.

 

[JoeU2004]

PS....

This approximates to 15 significant decimal digits, as
you have found. This is the way it is designed to work

I responded:

Sorry, but you are wrong.

.... Not that I would object to a calculation option that would do just that,
namely: force the result of all formulas to be rounded to 15 significant
digits, not unlike the "Precision as displayed" option, but more generally
applied.

That would legitimatize some of the heuristics that Excel has implemented to
try to ameliorate the aberrations due to binary floating point arithmetic.
For example, as I noted in response to Jerry elsewhere in this thread, the
presumptive behavior of INT would work in my specific example --
A1-INT(A1) -- if the formula in A1 had been rounded to 15 significant
digits.

I imagine the performance cost would be equivalent to the performance cost
of "Precision as displayed".

Arguably, there would be still an issue with subexpressions in formulas.
For example, 123456789 - 0.0000004 - INT(123456789 - 0.0000004) might still
be a problem, at least not without an expensive solution.

Moreover, it would not mask the effects of all aberrations caused by binary
floating point arithmetic. But it should eliminate the "hidden" effects,
effects that cannot be seen even when formatting the cell to display 15
significant digits.

In any case, Jerry's comments do suggest a work-around to the specific
anomaly that I presented in the original posting, to wit:
VALUE(A1&"")-INT(A1) is well-behaved, given the presumptive behavior of INT.


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

 

[Charles Williams]

It's sometimes interesting to try to work out exactly what chain of
calculations Excel is performing under the covers, but I think (without
going into the gory details) that Excel is working as designed in your
example.

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.

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

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

 

[Charles Williams]

BTW, to answer your original question, in both Excel 2007 SP2 and Excel 2010
Technical Preview

A1=123456789-0.0000004
=A1-INT(A1) gives -4.02331E-07