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.

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

"JoeU2004" wrote in message
...
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.

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.

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

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.

"Charles Williams" wrote in message
...
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 -----

"Charles Williams" wrote in message
...
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

"JoeU2004" wrote in message
...
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.

On Sat, 10 Oct 2009 01:19:54 -0700, "JoeU2004" wrote:

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

On Sat, 10 Oct 2009 02:55:02 -0700, Jerry W. Lewis
wrote:

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

That 's strange:

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

Excel 2007 SP2

--
Kind regards,

Niek Otten
Microsoft MVP - Excel


"Ron Rosenfeld" wrote in message
...
On Sat, 10 Oct 2009 01:19:54 -0700, "JoeU2004"
wrote:

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

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

"JoeU2004" wrote:
....
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.
....

"Ron Rosenfeld" wrote:
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 -----

"Ron Rosenfeld" wrote in message
...
On Sat, 10 Oct 2009 01:19:54 -0700, "JoeU2004"
wrote:

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

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

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.

"Jerry W. Lewis" wrote:
=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 -----

"Jerry W. Lewis" wrote in message
...
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

"JoeU2004" wrote:
...
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.
...

"Niek Otten" wrote in message
...
For =A1-INT(A1) I get -4.02331E-07
Excel 2007 SP2

Thanks. I consider that is dispositive.


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

"Niek Otten" wrote in message
...
That 's strange:

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

Excel 2007 SP2

--
Kind regards,

Niek Otten
Microsoft MVP - Excel


"Ron Rosenfeld" wrote in message
...
On Sat, 10 Oct 2009 01:19:54 -0700, "JoeU2004"
wrote:

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

On Sat, 10 Oct 2009 04:38:18 -0700, "JoeU2004" wrote:

"Ron Rosenfeld" wrote:
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.

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

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

"Mike H" wrote in 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

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

PS....

"Charles Williams" wrote:
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 -----

"JoeU2004" wrote in message
...
"Charles Williams" wrote in message
...
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 -----

"Charles Williams" wrote in message
...
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

"JoeU2004" wrote in message
...
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.

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

"JoeU2004" wrote in message
...
PS....

"Charles Williams" wrote:
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 -----

"JoeU2004" wrote in message
...
"Charles Williams" wrote in message
...
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 -----

"Charles Williams" wrote in message
...
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

"JoeU2004" wrote in message
...
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.

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

"JoeU2004" wrote:

"Jerry W. Lewis" wrote:
=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.

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

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

"Charles Williams" wrote:
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 -----

"Charles Williams" wrote in message
...
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

"JoeU2004" wrote in message
...
PS....

"Charles Williams" wrote:
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 -----

"JoeU2004" wrote in message
...
"Charles Williams" wrote in message
...
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 -----

"Charles Williams" wrote in message
...
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

"JoeU2004" wrote in message
...
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.

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

"Jerry W. Lewis" wrote:
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 -----

"Jerry W. Lewis" wrote in message
...
"JoeU2004" wrote:

"Jerry W. Lewis" wrote:
=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.

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

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

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" wrote:

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

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

"Jerry W. Lewis" wrote in message
...
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" wrote:

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

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