Excel Calculation issues

[BaileyPouter]

I'm getting confusing results from Excel. The real issue is in a
SumIf formula that I have where values that should match the criteria
I specify are not being found. But I've simplified the issue into a
relatively simple situation that seems to show a real flaw in Excel's
calculations.

If you format a cell as a number showing 10 decimal places, what would
you expect the following formula to return?

=ROUND(40179.0044000199,8)

I would think that it would return 40179.00440002. But what I see
instead is 40179.0044000199.

Does anyone have any idea what could be going on here? Precision as
displayed is unchecked.

Thanks for any thoughts...

 

[Ron Rosenfeld]

I'm getting confusing results from Excel. The real issue is in a
SumIf formula that I have where values that should match the criteria
I specify are not being found. But I've simplified the issue into a
relatively simple situation that seems to show a real flaw in Excel's
calculations.

If you format a cell as a number showing 10 decimal places, what would
you expect the following formula to return?

=ROUND(40179.0044000199,8)

I would think that it would return 40179.00440002. But what I see
instead is 40179.0044000199.

Does anyone have any idea what could be going on here? Precision as
displayed is unchecked.

Thanks for any thoughts...

You don't even have to round it.

Just type 40179.00440002 into some cell. It changes to 40179.0044000199

--ron

 

[Bernard Liengme]

And yet with 41234.5678901234 in A5
=ROUND(A5,8)
displays 41234.56789012 (8 decimal places)
And if you format the cell to increase decimals all you get is additional
zeros as expected

Wonder what is special about your value?
best wishes

 

[JoeU2004]

I would think that it would return 40179.00440002.
But what I see instead is 40179.0044000199.

This is a side-effect of the way that Excel (and most applications)
represent numbers internally and perform arithmetic on binary computers.

Generally, numbers with decimal fractions cannot be represented exactly.
Instead, they are represented internally as the sum of 53 consecutive powers
of 2 ("bits"). But it is a finite number of bits, the representation is
usually approximate.

As it happens, 40179.00440002 and 40179.0044000199 have the same internal
representation, namely 40179.0044000198,97730089724063873291015625. (The
comma is my way of demarcating the first 15 significant digits on the left.)

If you format the cell with 8 decimal places, you will see 40179.00440002.
If you format with 10 decimal places, you will see 40179.0044000199. They
are the same value.

If you format a cell as a number showing 10 decimal places,
what would you expect the following formula to return?
=ROUND(40179.0044000199,8)

ROUND(...,8) ensures that the result of the expression is the same as if you
entered it as a constant with the same number of decimal places.


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

 

[JoeU2004]

PS....

As it happens, 40179.00440002 and 40179.0044000199
have the same internal representation, namely
40179.0044000198,97730089724063873291015625.

Although that statement is factually correct, it is not clear to me why it
should be the case.

The value 40179.0044000199 + 15*2^-37 is exactly
40179.0044000200,068694539368152618408203125 internally, and it is displayed
as 40179.0044000200.

40179.0044000199 + 14*2^-37 is an even closer approximation, namely
40179.0044000199,995934963226318359375. But oddly, it is displayed as
40179.0044000199.

FYI, 40179.0044000199 + 0.0000000001 has the same internal representation as
40179.0044000199 + 14*2^-37. That is not too surprising.

I need to give this more thought. But I need to run now (literally).


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

 

[Dana DeLouis]

I would think that it would return 40179.00440002. But what I see
Hi. I think this is the same bug that's been around for a while.
You don't get it if the number is > 2^16 (65536)

So, 65000.00440002 has its display changed, but
66000.00440002 should stay the same.

= = = = =
Dana DeLouis

 

[Ron Rosenfeld]

Hi. I think this is the same bug that's been around for a while.
You don't get it if the number is > 2^16 (65536)

So, 65000.00440002 has its display changed, but
66000.00440002 should stay the same.

One would think they would correct this bug.
--ron

 

[JoeU2004]

Errata...

As it happens, 40179.00440002 and 40179.0044000199 have the same internal
representation, namely 40179.0044000198,97730089724063873291015625.

Don't know how I came to that conclusion. I don't see that now. Klunk!

In any case, scratch this "explanation". Dana has the answer.


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

 

[JoeU2004]

I think this is the same bug that's been around for a while.
You don't get it if the number is > 2^16 (65536)

So, 65000.00440002 has its display changed, but
66000.00440002 should stay the same.

Okay. But what exactly is the specification of the defect?

I can see that this misbehaves for xxxxx.00440002 where 4096<=xxxxx<=65535.

I suspect that is not the only decimal fraction that is problematic. But
I'm not seeing a pattern.

For example, 4096.00440002 is displayed incorrectly as 4096.0044000199. Its
internal value ends in 1999,9693496..., and its hex representation ends in
&h205C1600. (Digits to the left of the comma are last of the first 15
significant digits.)

But 4096.00440007 displays correctly as 4096.00440007. Its internal value
ends with 6999,997422099..., and its hex representation ends in &h205CECC0.


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

 

[Dana DeLouis]

I suspect that is not the only decimal fraction that is problematic.

But I'm not seeing a pattern.

Hi. Yes, there are more. I remember that the more common problem
occurs between 2^15 and 2^16.
(ie 32768 to 65536)

Numbers ending in the following digits also have this "issue."

{0.098, 0.223, 0.348, 0.473, 0.598, 0.723, 0.848, 0.973}

They are all offset by 1/8.

So, a number like 30000.098 will display ok.
40000.098 will not display properly.

I noticed that in Excel for Mac, this is not a problem.
Hmmmm. Wonder why?

= = = = = = = = =
Dana DeLouis

 

[JoeU2004]

I'm getting confusing results from Excel.
The real issue is in a SumIf formula that I have
where values that should match the criteria I
specify are not being found.

As I explain below, I suspect your "simplification" is just a distraction.
I suggest we get back to your real problem.

I believe you are saying that you have something like:

=SUMIF(A1:A100,"=1.23")

and you are wondering why cells that __display__ 1.23 are not included in
the sum.

If that's the case, I think the best solution is probably to round the
non-constant results in each and all of the cells in the range.

In general, the key is: if you round to N decimal places, you should
compare with a constant or another result that is also rounded to N decimal
places.

Moreover, you should round prolifically in order to avoid propagating
numerical abberations that cannot be avoided -- like 40197.00440002 is not
exactly that, but 40179.0044000199,995934963226318359375 instead.

If you sum or perform some other arithmetic over very large ranges -- 1000s
of cells -- the numerical abberations can add up to a significant abberation
before you have a chance to correct them by rounding. But if you round the
results of each non-constant cell, and if you round to relatively few
decimal places (e.g. 2 for financials), the chances of that happening are
small.

If this is not sufficient to solve your situation, I suggest that you post
the details of your original problem, and we can offer specific suggestions.

BTW, there are alternatives to rounding all formulas. There are pros and
cons of all of these alternatives, including the rounding that I suggest
here.

I've simplified the issue into a relatively simple
situation that seems to show a real flaw in Excel's
calculations.
[....]
=ROUND(40179.0044000199,8)
I would think that it would return 40179.00440002.
But what I see instead is 40179.0044000199.

As Dana explained, it appears that you have stumbled upon a known(?) defect
in the way that Excel formats certain values.

However, ROUND(40179.0044000199,8)=40179.00440002 is indeed TRUE, and
ROUND(40179.0044000199,8)=40179.0044000199 is indeed FALSE.

This is because the internal representations of 40179.00440002 and
40179.0044000199 are truly different, regardless of what Excel displays and
contrary to what I wrote previous. (Sorry for the misdirection. It was a
silly mistake; "haste makes waste".)

The internal representation of 40179.00440002 and ROUND(40179.004000199,8)
is 40179.0044000199,995934963226318359375, regardless of how it is
displayed. And that is the closest approximation of 40179.0040002 permitted
by the internal binary representation.

(Interestingly, 40179.0040002 + 2^-37, the next closest approximation, does
display as 40179.004000200 when formatted with 10 decimal places. But I
disgress....)

The internal representation of 40179.0044000199, written as a constant, is
40179.0044000198,97730089724063873291015625. That, too, is the closest
approximation of 40179.0044000199.

So as interesting all this may be, I suspect this is just a distraction from
your real problem. In trying to distill your original problem, you have
wandered down a rat hole needlessly. Happens to me all the time .


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

 

[JoeU2004]

Numbers ending in the following digits also have this "issue."
{0.098, 0.223, 0.348, 0.473, 0.598, 0.723, 0.848, 0.973}

Can you point me to credible documentation of this defect online?

They are all offset by 1/8.

Hmm, 0.00440002 is not an integral multiple of 0.125 from 0.098. I doubt
the rule is anything quite that simple, based on what my experiments for
this thread.

I noticed that in Excel for Mac, this is not a problem.
Hmmmm. Wonder why?

Probably differences in the software or even hardware used for converting
from binary to text.

I suspect the latter because of the similarities that I see in examples that
have different results (correctly v. incorrectly formatted). The Intel FPU
uses 80-bit registers for floating point arithmetic. Those results must be
crammed into the standard 64-bit form when they are stored into memory. The
Intel FPU provides several alternatives for rounding the 80-bit value into a
64-bit. That might be a factor. But that's a WAG.

But software is another real possibility. Apple engineers are renowned for
paying attention to detail, hacking algorithms to make them behave more
humanlike. They might also be mindful of differences, if any, between the
PowerPC and Intel FPUs. I can imagine that the Mac conversion routines
tweak the result as needed, as I do manually. Adding 1 to the
least-signifcant bit sometimes make the necessary difference for
Windows/Excel conversion routines. Of course, that is also a WAG.

It is difficult for me to reverse-engineer this and explore variations of
the algorithm. My own method of converting to an exact value is probably
very different from "typical" algorithms. And I no longer have access to a
credible (read: Unix) implementation that I could leverage.

I can work on this on the back-burner over time. But I wonder if someone
like Jerry has always figured this out to a "T".


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

 

[JoeU2004]

Errata....

I noticed that in Excel for Mac, this is not a problem.
Hmmmm. Wonder why?

Probably differences in the software or even hardware [...].
I suspect the latter [....]

Wrong again! VB Format() does the right thing with 40179.00440002. So the
problem is specific to the algorithm used by Excel. No idea what it could
be.

(Arguably, VB does make better use of the 80-bit flt pt registers. But on
second thought, I think that's a misdirection anyway.)


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

 

[Jerry W. Lewis]

This is a well known Excel display bug, where the underlying value is correct
but is displayed incorrectly. MS minimizes it in
http://support.microsoft.com/kb/161234
acknowleging only that it occurs with numbers between 2^15 and 2^16 that end
in .848 when in fact it occurs with millions of decimal fractions occurring
over a much larger range. As you have discovered, you can easily identify
these display bugs by the discrepancy between =cellref&"" and VBA's
CStr(cellref).

Jerry

Errata....

I noticed that in Excel for Mac, this is not a problem.
Hmmmm. Wonder why?

Probably differences in the software or even hardware [...].
I suspect the latter [....]

Wrong again! VB Format() does the right thing with 40179.00440002. So the
problem is specific to the algorithm used by Excel. No idea what it could
be.

(Arguably, VB does make better use of the 80-bit flt pt registers. But on
second thought, I think that's a misdirection anyway.)


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

Can you point me to credible documentation of this defect online?



Hmm, 0.00440002 is not an integral multiple of 0.125 from 0.098. I doubt
the rule is anything quite that simple, based on what my experiments for
this thread.



Probably differences in the software or even hardware used for converting
from binary to text.

I suspect the latter because of the similarities that I see in examples
that have different results (correctly v. incorrectly formatted). The
Intel FPU uses 80-bit registers for floating point arithmetic. Those
results must be crammed into the standard 64-bit form when they are stored
into memory. The Intel FPU provides several alternatives for rounding the
80-bit value into a 64-bit. That might be a factor. But that's a WAG.

But software is another real possibility. Apple engineers are renowned
for paying attention to detail, hacking algorithms to make them behave
more humanlike. They might also be mindful of differences, if any,
between the PowerPC and Intel FPUs. I can imagine that the Mac conversion
routines tweak the result as needed, as I do manually. Adding 1 to the
least-signifcant bit sometimes make the necessary difference for
Windows/Excel conversion routines. Of course, that is also a WAG.

It is difficult for me to reverse-engineer this and explore variations of
the algorithm. My own method of converting to an exact value is probably
very different from "typical" algorithms. And I no longer have access to
a credible (read: Unix) implementation that I could leverage.

I can work on this on the back-burner over time. But I wonder if someone
like Jerry has always figured this out to a "T".


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

 

[JoeU2004]

This is a well known Excel display bug, where the underlying value
is correct but is displayed incorrectly. MS minimizes it in
http://support.microsoft.com/kb/161234
acknowleging only that it occurs with numbers between 2^15 and
2^16 that end in .848

Thanks for the pointer.

when in fact it occurs with millions of decimal fractions occurring
over a much larger range.

Right. Have you root-caused or otherwise fully-characterized the problem?

Dana mentioned that the problem documented by KB 161234 affects decimal
fractions that are multiples (0 to 7) of 0.125 from 0.098. But I suspect
that is because that alters only higher-order bits.

The decimal fraction 0.00440002 bears no obvious relationship to 0.098 (or
0.848) that I can see. Moreover, with that fraction, the range of affected
integral numbers is 4096 to 65535 inclusive (2^12 to 2^16-1), not 32768 to
65535.

For numbers below 10000, we need to format to 11 decimal places, not 10 dp,
to see the problem. Generally, I suspect that when there is problem, it
arises when formatting to the maximum of 15 significant digits. The easiest
way to do that is to choose Scientific format with 14 decimal places.

I suspect the root cause of the problem is how the conversion algorithm
handles lower-order bits after determining the 15th significant digit.


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

This is a well known Excel display bug, where the underlying value is
correct
but is displayed incorrectly. MS minimizes it in
http://support.microsoft.com/kb/161234
acknowleging only that it occurs with numbers between 2^15 and 2^16 that
end
in .848 when in fact it occurs with millions of decimal fractions
occurring
over a much larger range. As you have discovered, you can easily identify
these display bugs by the discrepancy between =cellref&"" and VBA's
CStr(cellref).

Jerry

Errata....

I noticed that in Excel for Mac, this is not a problem.
Hmmmm. Wonder why?

Probably differences in the software or even hardware [...].
I suspect the latter [....]

Wrong again! VB Format() does the right thing with 40179.00440002. So
the
problem is specific to the algorithm used by Excel. No idea what it
could
be.

(Arguably, VB does make better use of the 80-bit flt pt registers. But
on
second thought, I think that's a misdirection anyway.)


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

Numbers ending in the following digits also have this "issue."
{0.098, 0.223, 0.348, 0.473, 0.598, 0.723, 0.848, 0.973}

Can you point me to credible documentation of this defect online?


They are all offset by 1/8.

Hmm, 0.00440002 is not an integral multiple of 0.125 from 0.098. I
doubt
the rule is anything quite that simple, based on what my experiments
for
this thread.


I noticed that in Excel for Mac, this is not a problem.
Hmmmm. Wonder why?

Probably differences in the software or even hardware used for
converting
from binary to text.

I suspect the latter because of the similarities that I see in examples
that have different results (correctly v. incorrectly formatted). The
Intel FPU uses 80-bit registers for floating point arithmetic. Those
results must be crammed into the standard 64-bit form when they are
stored
into memory. The Intel FPU provides several alternatives for rounding
the
80-bit value into a 64-bit. That might be a factor. But that's a WAG.

But software is another real possibility. Apple engineers are renowned
for paying attention to detail, hacking algorithms to make them behave
more humanlike. They might also be mindful of differences, if any,
between the PowerPC and Intel FPUs. I can imagine that the Mac
conversion
routines tweak the result as needed, as I do manually. Adding 1 to the
least-signifcant bit sometimes make the necessary difference for
Windows/Excel conversion routines. Of course, that is also a WAG.

It is difficult for me to reverse-engineer this and explore variations
of
the algorithm. My own method of converting to an exact value is
probably
very different from "typical" algorithms. And I no longer have access
to
a credible (read: Unix) implementation that I could leverage.

I can work on this on the back-burner over time. But I wonder if
someone
like Jerry has always figured this out to a "T".


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

I suspect that is not the only decimal fraction that is problematic.
But I'm not seeing a pattern.

Hi. Yes, there are more. I remember that the more common problem
occurs
between 2^15 and 2^16.
(ie 32768 to 65536)

Numbers ending in the following digits also have this "issue."

{0.098, 0.223, 0.348, 0.473, 0.598, 0.723, 0.848, 0.973}

They are all offset by 1/8.

So, a number like 30000.098 will display ok.
40000.098 will not display properly.

I noticed that in Excel for Mac, this is not a problem.
Hmmmm. Wonder why?

= = = = = = = = =
Dana DeLouis



JoeU2004 wrote:
I would think that it would return 40179.00440002.
But what I see instead is 40179.0044000199.

I think this is the same bug that's been around for a while.
You don't get it if the number is > 2^16 (65536)

So, 65000.00440002 has its display changed, but
66000.00440002 should stay the same.

Okay. But what exactly is the specification of the defect?

I can see that this misbehaves for xxxxx.00440002 where
4096<=xxxxx<=65535.

I suspect that is not the only decimal fraction that is problematic.
But I'm not seeing a pattern.

For example, 4096.00440002 is displayed incorrectly as
4096.0044000199.
Its internal value ends in 1999,9693496..., and its hex
representation
ends in &h205C1600. (Digits to the left of the comma are last of the
first 15 significant digits.)

But 4096.00440007 displays correctly as 4096.00440007. Its internal
value ends with 6999,997422099..., and its hex representation ends in
&h205CECC0.


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

I would think that it would return 40179.00440002. But what I
see
instead is 40179.0044000199.

Hi. I think this is the same bug that's been around for a while.
You don't get it if the number is > 2^16 (65536)

So, 65000.00440002 has its display changed, but
66000.00440002 should stay the same.

= = = = =
Dana DeLouis

Ron Rosenfeld wrote:
On Mon, 10 Aug 2009 10:45:54 -0700 (PDT), BaileyPouter

I'm getting confusing results from Excel. The real issue is in a
SumIf formula that I have where values that should match the
criteria
I specify are not being found. But I've simplified the issue into
a
relatively simple situation that seems to show a real flaw in
Excel's
calculations.

If you format a cell as a number showing 10 decimal places, what
would
you expect the following formula to return?

=ROUND(40179.0044000199,8)

I would think that it would return 40179.00440002. But what I see
instead is 40179.0044000199.

Does anyone have any idea what could be going on here? Precision
as
displayed is unchecked.

Thanks for any thoughts...

You don't even have to round it.

Just type 40179.00440002 into some cell. It changes to
40179.0044000199

--ron

 

[JoeU2004]

Errata....

I suspect the root cause of the problem is how the conversion algorithm
handles lower-order bits after determining the 15th significant digit.

Wrong again. I'm beginning to feel like "86" , aka Maxwell Smart . But
that's the nature of my approach to root-causing problems: assert a
hypothesis, then create an experiment to verify or disprove the hypothesis.
It works better when I'm familiar with the internal implementation .

I don't know if the Excel conversion algorithm is anything like mine; I'd be
surprised if it is. But I do expect that it processes the internal binary
form bit-by-bit in some way, as I do.

The following output shows how my algorithm builds up the conversion in an
accumulator, starting with the phantom one-bit to the left of the binary
radix point ("bit 52") and proceeding left-to-right until the last one-bit
(bit 0 or higher).

The first output sequence (format: bitnum : accumulator) is for
40179.00440002, which Excel formats incorrectly.

The second output sequence is for 72949.0040002, which Excel formats
correctly. I chose the second example because it has the same bit pattern
as the first example, albeit shifted by one bit.

The key thing to note is: for these numbers, the 15th significant digit
does not "settle down" until the right-most one-bit. So if Excel had
stopped processing prematurely, it would have displayed a completely
incorrect representation, e.g. 40179.0044000195 -- again, assuming that the
Excel is something like mine. (Obviously it is not!)


PS: I wrote: ``for these numbers, the 15th significant digit does not
"settle down" until the right-most one-bit``. That is not necessarily
always the case.

Consider the third output sequence for 40179.00440002 + 2^-35 + 2^-36 +
2^-37, which Excel formats correctly, BTW.

The 15th significant digit is zero after converting bit 2, and it remains
zero after converting bit 1. This is the situation that I hypothesized
might cause a poorly-designed conversion algorithm (i.e. Excel's) to stop
prematurely.

If it had, there would be only an off-by-one error in the 15th significant
digit, as we see with example of 40179.0040002. That is because the
conversion of bit 0 changes the 16th significant digit materially insofar it
changes how the 15th significant digit should be rounded.

However, since Excel does correctly format this value with a 1 in the 15th
significant digit, this demonstrates again that Excel does not normally stop
the conversion process prematurely. (And/or its algorithm is significantly
different from mine.)

QE(not)D


Examples (format: bitnum : accumulator).....


Output#1: Conversion of 40179.004002

52 : 32768
49 : 36864
48 : 38912
47 : 39936
44 : 40064
43 : 40128
42 : 40160
41 : 40176
38 : 40178
37 : 40179
29 : 40179.00390625
26 : 40179.0043945312,5
19 : 40179.0043983459,47265625
17 : 40179.0043992996,2158203125
16 : 40179.0043997764,58740234375
15 : 40179.0044000148,773193359375
9 : 40179.0044000186,026096343994140625
7 : 40179.0044000195,33932209014892578125
6 : 40179.0044000199,995934963226318359375


Output #2: Conversion of 72947.00440002

52 : 65536
48 : 69632
47 : 71680
46 : 72704
43 : 72832
42 : 72896
41 : 72928
40 : 72944
37 : 72946
36 : 72947
28 : 72947.00390625
25 : 72947.0043945312,5
18 : 72947.0043983459,47265625
16 : 72947.0043992996,2158203125
15 : 72947.0043997764,58740234375
14 : 72947.0044000148,773193359375
8 : 72947.0044000186,026096343994140625
6 : 72947.0044000195,33932209014892578125
5 : 72947.0044000199,995934963226318359375


Output #3: Conversion of 40179.0040002 + 2^-35 + 2^-36 + 2^-37

52 : 32768
49 : 36864
48 : 38912
47 : 39936
44 : 40064
43 : 40128
42 : 40160
41 : 40176
38 : 40178
37 : 40179
2 : 40179.0000000000,2910383045673370361328125
1 : 40179.0000000000,43655745685100555419921875
0 : 40179.0000000000,509317032992839813232421875


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

This is a well known Excel display bug, where the underlying value
is correct but is displayed incorrectly. MS minimizes it in
http://support.microsoft.com/kb/161234
acknowleging only that it occurs with numbers between 2^15 and
2^16 that end in .848

Thanks for the pointer.

when in fact it occurs with millions of decimal fractions occurring
over a much larger range.

Right. Have you root-caused or otherwise fully-characterized the problem?

Dana mentioned that the problem documented by KB 161234 affects decimal
fractions that are multiples (0 to 7) of 0.125 from 0.098. But I suspect
that is because that alters only higher-order bits.

The decimal fraction 0.00440002 bears no obvious relationship to 0.098 (or
0.848) that I can see. Moreover, with that fraction, the range of
affected integral numbers is 4096 to 65535 inclusive (2^12 to 2^16-1), not
32768 to 65535.

For numbers below 10000, we need to format to 11 decimal places, not 10
dp, to see the problem. Generally, I suspect that when there is problem,
it arises when formatting to the maximum of 15 significant digits. The
easiest way to do that is to choose Scientific format with 14 decimal
places.

I suspect the root cause of the problem is how the conversion algorithm
handles lower-order bits after determining the 15th significant digit.


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

This is a well known Excel display bug, where the underlying value is
correct
but is displayed incorrectly. MS minimizes it in
http://support.microsoft.com/kb/161234
acknowleging only that it occurs with numbers between 2^15 and 2^16 that
end
in .848 when in fact it occurs with millions of decimal fractions
occurring
over a much larger range. As you have discovered, you can easily
identify
these display bugs by the discrepancy between =cellref&"" and VBA's
CStr(cellref).

Jerry

Errata....

I wrote:
I noticed that in Excel for Mac, this is not a problem.
Hmmmm. Wonder why?

Probably differences in the software or even hardware [...].
I suspect the latter [....]

Wrong again! VB Format() does the right thing with 40179.00440002. So
the
problem is specific to the algorithm used by Excel. No idea what it
could
be.

(Arguably, VB does make better use of the 80-bit flt pt registers. But
on
second thought, I think that's a misdirection anyway.)


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

Numbers ending in the following digits also have this "issue."
{0.098, 0.223, 0.348, 0.473, 0.598, 0.723, 0.848, 0.973}

Can you point me to credible documentation of this defect online?


They are all offset by 1/8.

Hmm, 0.00440002 is not an integral multiple of 0.125 from 0.098. I
doubt
the rule is anything quite that simple, based on what my experiments
for
this thread.


I noticed that in Excel for Mac, this is not a problem.
Hmmmm. Wonder why?

Probably differences in the software or even hardware used for
converting
from binary to text.

I suspect the latter because of the similarities that I see in
examples
that have different results (correctly v. incorrectly formatted). The
Intel FPU uses 80-bit registers for floating point arithmetic. Those
results must be crammed into the standard 64-bit form when they are
stored
into memory. The Intel FPU provides several alternatives for rounding
the
80-bit value into a 64-bit. That might be a factor. But that's a
WAG.

But software is another real possibility. Apple engineers are
renowned
for paying attention to detail, hacking algorithms to make them behave
more humanlike. They might also be mindful of differences, if any,
between the PowerPC and Intel FPUs. I can imagine that the Mac
conversion
routines tweak the result as needed, as I do manually. Adding 1 to
the
least-signifcant bit sometimes make the necessary difference for
Windows/Excel conversion routines. Of course, that is also a WAG.

It is difficult for me to reverse-engineer this and explore variations
of
the algorithm. My own method of converting to an exact value is
probably
very different from "typical" algorithms. And I no longer have access
to
a credible (read: Unix) implementation that I could leverage.

I can work on this on the back-burner over time. But I wonder if
someone
like Jerry has always figured this out to a "T".


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

I suspect that is not the only decimal fraction that is
problematic.
But I'm not seeing a pattern.

Hi. Yes, there are more. I remember that the more common problem
occurs
between 2^15 and 2^16.
(ie 32768 to 65536)

Numbers ending in the following digits also have this "issue."

{0.098, 0.223, 0.348, 0.473, 0.598, 0.723, 0.848, 0.973}

They are all offset by 1/8.

So, a number like 30000.098 will display ok.
40000.098 will not display properly.

I noticed that in Excel for Mac, this is not a problem.
Hmmmm. Wonder why?

= = = = = = = = =
Dana DeLouis



JoeU2004 wrote:
I would think that it would return 40179.00440002.
But what I see instead is 40179.0044000199.

I think this is the same bug that's been around for a while.
You don't get it if the number is > 2^16 (65536)

So, 65000.00440002 has its display changed, but
66000.00440002 should stay the same.

Okay. But what exactly is the specification of the defect?

I can see that this misbehaves for xxxxx.00440002 where
4096<=xxxxx<=65535.

I suspect that is not the only decimal fraction that is problematic.
But I'm not seeing a pattern.

For example, 4096.00440002 is displayed incorrectly as
4096.0044000199.
Its internal value ends in 1999,9693496..., and its hex
representation
ends in &h205C1600. (Digits to the left of the comma are last of
the
first 15 significant digits.)

But 4096.00440007 displays correctly as 4096.00440007. Its internal
value ends with 6999,997422099..., and its hex representation ends
in
&h205CECC0.


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

I would think that it would return 40179.00440002. But what I
see
instead is 40179.0044000199.

Hi. I think this is the same bug that's been around for a while.
You don't get it if the number is > 2^16 (65536)

So, 65000.00440002 has its display changed, but
66000.00440002 should stay the same.

= = = = =
Dana DeLouis

Ron Rosenfeld wrote:
On Mon, 10 Aug 2009 10:45:54 -0700 (PDT), BaileyPouter

I'm getting confusing results from Excel. The real issue is in a
SumIf formula that I have where values that should match the
criteria
I specify are not being found. But I've simplified the issue
into a
relatively simple situation that seems to show a real flaw in
Excel's
calculations.

If you format a cell as a number showing 10 decimal places, what
would
you expect the following formula to return?

=ROUND(40179.0044000199,8)

I would think that it would return 40179.00440002. But what I
see
instead is 40179.0044000199.

Does anyone have any idea what could be going on here? Precision
as
displayed is unchecked.

Thanks for any thoughts...

You don't even have to round it.

Just type 40179.00440002 into some cell. It changes to
40179.0044000199

--ron