Google Groups no longer supports new Usenet posts or subscriptions. Historical content remains viewable.
Dismiss

Bug in Excel 2007

4,002 views
Skip to first unread message

Molham Serry

unread,
Sep 22, 2007, 3:20:33 PM9/22/07
to
Simply when you try to multiply 850 by 77.1 excel display the result to be
100000 !!!


Bernard Liengme

unread,
Sep 22, 2007, 3:45:59 PM9/22/07
to
Confirmed!
best wishes
--
Bernard Liengme
Excel MVP
http://people.stfx.ca/bliengme

"Molham Serry" <mse...@contact.com.eg> wrote in message
news:834DAE15-EE9C-4996...@microsoft.com...

Dana DeLouis

unread,
Sep 22, 2007, 7:38:21 PM9/22/07
to
Wow! How did you find this major bug?
Even Sumproduct returned 100000.

=SUMPRODUCT(850,77.1)
=SUMPRODUCT(850,77.1,2,0.5)

(2*0.5 = 1)

This seems Major to me!
--
Dana DeLouis
Windows XP & Excel 2007


"Molham Serry" <mse...@contact.com.eg> wrote in message
news:834DAE15-EE9C-4996...@microsoft.com...

Wild Bill

unread,
Sep 22, 2007, 11:26:49 PM9/22/07
to
Bump - this sounds alarmingly serious. Maybe someone seeing this is
plugged in enough to call & get someone out of their bed or their bong.

Earl Kiosterud

unread,
Sep 22, 2007, 11:44:25 PM9/22/07
to
Sheesh! The correct answer, 65,535, happens to have two bytes worth of 1 digits in binary.
I wonder what that might have to do with it. I haven't installed 2007 yet (prescient?), so
I wonder if anyone tried other numbers that should produce the same answer, like 4 *
16383.75, or 222 * 295.2027?
--
Regards from Virginia Beach,

Earl Kiosterud
www.smokeylake.com


"Bernard Liengme" <blie...@stfx.TRUENORTH.ca> wrote in message
news:%23miNyEV$HHA....@TK2MSFTNGP03.phx.gbl...

Bernard Liengme

unread,
Sep 23, 2007, 8:46:37 AM9/23/07
to
This has now been reported to Microsoft
--
Bernard V Liengme
Microsoft Excel MVP
www.stfx.ca/people/bliengme
remove caps from email

"Molham Serry" <mse...@contact.com.eg> wrote in message
news:834DAE15-EE9C-4996...@microsoft.com...

Dana DeLouis

unread,
Sep 23, 2007, 8:55:33 AM9/23/07
to
There appears to be more of them. For example:

=5.1*12850
=10.2*6425
=20.4*3212.5
=40.8*1606.25
=77.1*850
=154.2*425
=212.5*308.4
=308.4*212.5
=425*154.2
..etc


What's even stranger is this: Suppose the formula is in A1.
=A1+1 returns 100001, which appears to show the formula is in fact 100000
and a very Serious problem.
And if you multiply be say, 2 you get something else:

=A1*2
returns 131070, as if A1 had 65535. (which it should have been)

=A1*1
Keeps it at 100000.

=A1-1 returns 65534

=A1/1 is still 100000
=A1/2 retuns 32767.5

Using MAX() on a range appears not to see 100000.

Very Serious!
--
Dana DeLouis


"Molham Serry" <mse...@contact.com.eg> wrote in message
news:834DAE15-EE9C-4996...@microsoft.com...

Dana DeLouis

unread,
Sep 23, 2007, 9:25:26 AM9/23/07
to
I also see that Excel 2007 still has this over 10+ year bug still in it:

=40000.223
=41000.348
=52000.723

Such numbers are converted to:

=40000.2229999999
=41000.3479999999
=52000.7229999999

--
Dana DeLouis


"Molham Serry" <mse...@contact.com.eg> wrote in message
news:834DAE15-EE9C-4996...@microsoft.com...

EpsilonRho

unread,
Sep 23, 2007, 12:01:11 PM9/23/07
to
Excel 2000 gives the correct answer.
Gene


Marcus Schöneborn

unread,
Sep 23, 2007, 12:02:43 PM9/23/07
to
»Dana DeLouis« <ddel...@bellsouth.net> wrote:
> I also see that Excel 2007 still has this over 10+ year bug still in it:
>
> =40000.223
> =41000.348
> =52000.723
>
> Such numbers are converted to:
>
> =40000.2229999999
> =41000.3479999999
> =52000.7229999999

That isn't a bug, but well-known floating point representation
inaccuracy. FPUs don't work in decimal.

The question just is whether Excel should work with a FPU's floating
point type, or make its own and calculate "exactly" in it. The latter
would be much slower, so it's doubtful if it is really worth the major
slowdown this would cause.

OTOH, Windows Calculator did get changed away from FPU's own floating
point types to its own arbitrary precision type. But it's only
calculating one cell at once...

Dana DeLouis

unread,
Sep 23, 2007, 12:42:18 PM9/23/07
to
> That isn't a bug, but well-known floating point representation

Hi. I believe Microsoft still considers it a "Problem." (ie Bug)
It affects numbers between 2^15 - 2^16 (32768 - 65536) that end with:

{.098, .223, .348, .473, .598, .723, .848, .973}

(Note: the endings are offset by 1/8)

--
Dana DeLouis


"Marcus Schöneborn" <div...@googlemail.com> wrote in message
news:slrnffd3h3....@hagger.rbi.informatik.uni-frankfurt.de...

Marcus Schöneborn

unread,
Sep 23, 2007, 3:41:17 PM9/23/07
to
»Dana DeLouis« <ddel...@bellsouth.net> wrote:
> > That isn't a bug, but well-known floating point representation
>
> Hi. I believe Microsoft still considers it a "Problem." (ie Bug)
> It affects numbers between 2^15 - 2^16 (32768 - 65536) that end with:
>
> {.098, .223, .348, .473, .598, .723, .848, .973}
>
> (Note: the endings are offset by 1/8)

If they really get turned into

40000.2229999999

it is an actual bug because:

as float: 40000.22265625000000000000
as double: 40000.22299999999813735485
as long double: 40000.22299999999999897682
Excel: 40000.2229999999 (rounded down)

so this would be LESS precision than IEEE doubles, or somewhat broken
rounding.

If there is an additional 98, it's no bug and a consequence of IEEE
doubles.

ilia

unread,
Sep 24, 2007, 10:53:19 AM9/24/07
to
Some more observations.

None of the other n-byte integers of all 1s have this issue.

A1 =((2^16-1)/425)*(850/2)
B1 = 65,535
C1 =(A1=B1)

C1 displays TRUE.

In VBE, typing Debug.Print ActiveCell.Value yields the correct
result. Likewise, the Application.Evaluate() method yields the
correct result.

This code (in worksheet's code module):

Public Sub testBug()
Me.Range("D5").Value = 850
Me.Range("D6").Value = 77.1
Debug.Print _
Application.WorksheetFunction.SumProduct(Me.Range("D5"),
Me.Range("D6"))
End Sub

Also displays 65,535.

Curious bug indeed!


On Sep 22, 11:44 pm, "Earl Kiosterud" <some...@nowhere.com> wrote:
> Sheesh! The correct answer, 65,535, happens to have two bytes worth of 1 digits in binary.
> I wonder what that might have to do with it. I haven't installed 2007 yet (prescient?), so
> I wonder if anyone tried other numbers that should produce the same answer, like 4 *
> 16383.75, or 222 * 295.2027?
> --
> Regards from Virginia Beach,
>
> Earl Kiosterudwww.smokeylake.com
>

> "Bernard Liengme" <blien...@stfx.TRUENORTH.ca> wrote in message


>
> news:%23miNyEV$HHA....@TK2MSFTNGP03.phx.gbl...
>
>
>
> > Confirmed!
> > best wishes
> > --
> > Bernard Liengme
> > Excel MVP
> >http://people.stfx.ca/bliengme
>
> > "Molham Serry" <mse...@contact.com.eg> wrote in message
> >news:834DAE15-EE9C-4996...@microsoft.com...

> >> Simply when you try to multiply 850 by 77.1 excel display the result to be 100000 !!!- Hide quoted text -
>
> - Show quoted text -


Dana DeLouis

unread,
Sep 24, 2007, 12:17:58 PM9/24/07
to
Hi. Just to add, code that joins both text and values could be affected by
this bug.

Sub Bug()
Dim x
[A1].Formula = "=77.1*850"
x = Range("A1").Value * 2
x = Range("A1").Value2 * 2

'// This is 200000 !!!
x = Range("A1").Text * 2
End Sub

Even numbers like this display this issue:
=SUMPRODUCT(0.41,65535/0.41)

--
Dana DeLouis


"ilia" <iasa...@gmail.com> wrote in message
news:1190645599.8...@50g2000hsm.googlegroups.com...

Dana DeLouis

unread,
Sep 24, 2007, 12:30:39 PM9/24/07
to
Hi. Took me a while to find it.

Numbers Ending in .848 Appear Incorrectly
http://support.microsoft.com/default.aspx?scid=kb;en-us;Q161234

STATUS
Microsoft has confirmed this to be a problem in the Microsoft products
listed at the beginning of this article. We are researching this problem ...

...Apparently since Microsoft Excel 5.0 <vbg>

--
Dana DeLouis


"Marcus Schöneborn" <div...@googlemail.com> wrote in message

news:slrnffdgat....@hagger.rbi.informatik.uni-frankfurt.de...

Earl Kiosterud

unread,
Sep 24, 2007, 5:44:52 PM9/24/07
to
Gene,

So does 2003. It appears to be a 2007 issue.


--
Regards from Virginia Beach,

Earl Kiosterud
www.smokeylake.com

"EpsilonRho" <Epsil...@nospam.ooo> wrote in message
news:b5wJi.51485$Um6....@newssvr12.news.prodigy.net...

*alan*

unread,
Sep 24, 2007, 7:41:21 PM9/24/07
to
It may be an insignificant coincidence, but I found it interesting that in
the article mentioned below, it's stated that the incorrect representation
of numbers ending in .848 occurs between "32,768 and 65,535". Isn't 65,535
the product that's getting misrepresented as 100000 when you calculate
850*77.1 in Excel2007?
Perhaps they were finally attempting to address the previous bug, and just
mucked it up even more?
--
alan

"Dana DeLouis" <ddel...@bellsouth.net> wrote in message
news:ucrJihs$HHA....@TK2MSFTNGP03.phx.gbl...

Harlan Grove

unread,
Sep 24, 2007, 9:58:31 PM9/24/07
to
"Dana DeLouis" <ddelo...@bellsouth.net> wrote...

>There appears to be more of them. For example:
...

Makes it appear this is a rendering issue.

>What's even stranger is this: Suppose the formula is in A1.
>=A1+1 returns 100001, which appears to show the formula is in fact
>100000
>and a very Serious problem.
>And if you multiply be say, 2 you get something else:
>
>=A1*2
>returns 131070, as if A1 had 65535. (which it should have been)

...
> =A1-1 returns 65534
...

Almost makes it appear some calculations use the .Text property rather
than the .Value property of A1. If so, definitely a bug.

Amusing, though

Harlan Grove

unread,
Sep 24, 2007, 10:10:02 PM9/24/07
to
@#$% laptop's @#$% mousepad!

"Dana DeLouis" <ddelo...@bellsouth.net> wrote...
>There appears to be more of them. For example:
...

Makes it appear this is a rendering issue.

>What's even stranger is this: Suppose the formula is in A1.
>=A1+1 returns 100001, which appears to show the formula is in fact
>100000
>and a very Serious problem.
>And if you multiply be say, 2 you get something else:
>
>=A1*2
>returns 131070, as if A1 had 65535. (which it should have been)
...
> =A1-1 returns 65534
...

Almost makes it appear some calculations use the .Text property rather
than the .Value property of A1. If so, definitely a bug.

Amusing, though, that A1+1 would be odd while A1-1 would be even.

Actually, I'd guess someone was trying to rewrite the 15 decimal
digits truncation code but screwed up, perhaps missing a bitwise AND
or XOR. I wonder how many programmers on the Excel team have any
experience with assembler? I'd guess not many, if any.

>Using MAX() on a range appears not to see 100000.

Meaning that if A1 contained this false 100000 and B1 contained 5,
MAX(A1:B1) returns 5? Or A2 contained 70000 and MAX(A1:A2) returns
70000?

>Very Serious!

Definitely. How soon do you suppose they'll fix this? We should start
a pool. I'll take not until SP2, which, based on SP1's release date
(not yet), I'd guess would be mid-Fall 2008.

wils...@gmail.com

unread,
Sep 24, 2007, 11:08:55 PM9/24/07
to

FP "bugs" abound. I wonder why Excel doesn't use BCD for its
calculations? Since the primary use of Excel is to calculate money,
and money is decimal....

oh wait. I just answered my own question. Rounding to 2 decimal places
generally eliminates that problem.

meg...@gmail.com

unread,
Sep 24, 2007, 11:11:07 PM9/24/07
to
This is simple rounding error. Down at the hardware level of PCs,
when working with floating point numbers, you are only guaranteed a
number of significant digits (varies depending on the size of the data
type). In this case, your numbers are good up to 7 digits (NNNNN.NN),
which fits with the double data type in C.

michael...@gmail.com

unread,
Sep 24, 2007, 11:41:43 PM9/24/07
to
On Sep 24, 10:11 pm, mega...@gmail.com wrote:
> This is simple rounding error. Down at the hardware level of PCs,
> when working with floating point numbers, you are only guaranteed a
> number of significant digits (varies depending on the size of the data
> type). In this case, your numbers are good up to 7 digits (NNNNN.NN),
> which fits with the double data type in C.

Complete and utter BS. As pointed out, the Excel representation holds
absolutely nothing in common with either the computers native FP
result, nor with the IEEE standard for floating point.
--
MB

Michael C

unread,
Sep 24, 2007, 11:50:40 PM9/24/07
to
"*alan*" <in_fla...@hotmail.com> wrote in message
news:BWXJi.55186$YL5....@newssvr29.news.prodigy.net...

> It may be an insignificant coincidence, but I found it interesting that in
> the article mentioned below, it's stated that the incorrect representation
> of numbers ending in .848 occurs between "32,768 and 65,535". Isn't
> 65,535 the product that's getting misrepresented as 100000 when you
> calculate 850*77.1 in Excel2007?
> Perhaps they were finally attempting to address the previous bug, and just
> mucked it up even more?

Bugs are usually found on boundaries. In this case 65535 is the smallest
number that fits into 16bits and 65536 requires 32 bit. Although excel will
use floating point for the numbers so it might not apply in this case there
is likely some significance.

Michael


Michael C

unread,
Sep 24, 2007, 11:52:18 PM9/24/07
to
"Harlan Grove" <hrl...@aol.com> wrote in message
news:1190686202....@19g2000hsx.googlegroups.com...

> Actually, I'd guess someone was trying to rewrite the 15 decimal
> digits truncation code but screwed up, perhaps missing a bitwise AND
> or XOR. I wonder how many programmers on the Excel team have any
> experience with assembler? I'd guess not many, if any.

I'd be EXTREMELY suprised if not one person or even only a few on the excel
team had experience with assembler. Remember all these programmers would be
using C which has all the power of assembler with the ease of use of
assembler.

Michael


Michael C

unread,
Sep 25, 2007, 12:01:07 AM9/25/07
to
<michael...@gmail.com> wrote in message
news:1190691703....@r29g2000hsg.googlegroups.com...

> On Sep 24, 10:11 pm, mega...@gmail.com wrote:
>> This is simple rounding error. Down at the hardware level of PCs,
>> when working with floating point numbers, you are only guaranteed a
>> number of significant digits (varies depending on the size of the data
>> type). In this case, your numbers are good up to 7 digits (NNNNN.NN),
>> which fits with the double data type in C.
>
> Complete and utter BS. As pointed out, the Excel representation holds
> absolutely nothing in common with either the computers native FP
> result, nor with the IEEE standard for floating point.

Are you saying excel doesn't use the FP processor? How then did the previous
intel floating point bug appear in excel?

Michael


Harlan Grove

unread,
Sep 25, 2007, 1:55:05 AM9/25/07
to
"Michael C" <m...@nospam.com> wrote...
><michael.e.br...@gmail.com> wrote in message
...

>>Complete and utter BS. As pointed out, the Excel representation
>>holds absolutely nothing in common with either the computers native
>>FP result, nor with the IEEE standard for floating point.
>
>Are you saying excel doesn't use the FP processor? How then did the
>previous intel floating point bug appear in excel?

Excel uses the FPU for INTERMEDIATE calculations, but the final value
of any cell formula gets passed through some, er, MSFT value-added
processing before it becomes the cell's value. This is how
nonparenthesized expressions are implicitly rounded to 15 decimal
digits. Also, and quite unfortunately, some built-in function calls
perform additional and usually unnecessary postprocessing of FPU
results, e.g., MOD(2^30-1,3), which returns #NUM! because MOD can't
handle arguments that produce quotients comfortably within 32-bit
integer or 64-bit double precision bounds even though this is well
within the FPU's capabilities.

I don't think this is the case for 850*77.1. I suspect some bright
spark on the Excel development team decided to rewrite their machine
representation-to-human representation internal number formatting
function, and they screwed it up somehow.

What really concerns me is what Dana DeLouis pointed out: with A1
containing =850*77.1, =A1+1 returns 100001 while =A1-1 returns 65534.
That's VERY, VERY BAD because it implies the value in A1 isn't just
fed directly to the FPU stack. It's subjected to, er, value-added
preprocessing which seems to differ depending on what the precise
operation is. What I'm most worried about, given Dana's other
examples, is whether someone thought they were doing Excel users a
favor by treating increment (+1), decrement (-1), idempotent
operations (+0, *1, ^1), binary left shift (*2) and binary right shift
(/2) as special cases in the formula parser. MSFT *DID* rewrite parts
of the formula parser in XL2007, which is how they lifted the 7 nested
function call limit etc. I'd bet they rewrote too much of it.

Sharath

unread,
Sep 25, 2007, 2:16:49 AM9/25/07
to
On Sep 23, 5:20 am, "Molham Serry" <mse...@contact.com.eg> wrote:
> Simply when you try to multiply 850 by 77.1 excel display the result to be
> 100000 !!!

Guess this bug was introduced recently.

I use Excel 2002 (10.6823.6626) at work and it shows the answers
correctly as 65535.

-Sharath

Jerker Pihl

unread,
Sep 25, 2007, 4:18:38 AM9/25/07
to
On Sep 22, 9:20 pm, "Molham Serry" <mse...@contact.com.eg> wrote:
> Simply when you try to multiply 850 by 77.1 excel display the result to be
> 100000 !!!

With an old office xp excel I get the correct answer :-D
Regards
el0

Harlan Grove

unread,
Sep 25, 2007, 4:47:38 AM9/25/07
to
"Michael C" <mi...@nospam.com> wrote...
...
> . . . with the ease of use of assembler.

Indeed!

And when it comes to bitwise manipulation, LESS clarity than assembler.


agni.asgeirsson

unread,
Sep 25, 2007, 5:22:46 AM9/25/07
to
On Sep 23, 4:01 pm, "EpsilonRho" <Epsilon...@nospam.ooo> wrote:
> Excel 2000 gives the correct answer.
> Gene

Excel 2003 Sp2 gives the correct answer for 850*77.1, but when I
format tha number as Hex with Dec2Hex I get FFFE which is one off!.
Dec2Hex(850*77.1+1) results FFFF but Dec2Hex(850*77.1+2) results the
correct value of 10001.

Agni

Peter Ross

unread,
Sep 25, 2007, 5:49:23 AM9/25/07
to
On Sep 22, 8:20 pm, "Molham Serry" <mse...@contact.com.eg> wrote:

> Simply when you try to multiply 850 by 77.1 excel display the result to be
> 100000 !!!

I wonder if this could be related to another oddity of Excel, present
in 2007 and in various earlier versions.
Choose a number x, use Excel to calculate the STDEV of the numbers x
+1, x+2, .., x+10. The correct
answer should be 3.027650354.. However, there are various jackpot
values of x that give other answers.
For example, x=9989999999223 gives a STDEV around 123575.8667. If you
vary that x slightly, say by adding
0.05, the STDEV changes to other very large values.

Excel seems capable of working with these sorts of large numbers; for
the above x, calculate y = 49*x*x
and then z = SQRT(y)/7 and you recover x correctly. The problem does
not seem to be due to an earlier
flaw, now fixed by Microsoft, of using a poor choice of method of
calculating STDEV.

At a guess, it appears to be an issue to do with internal change of
representation.


dou...@gmail.com

unread,
Sep 25, 2007, 5:59:34 AM9/25/07
to
It's interesting to write a short function that simply adds 1 to a
cell containing the erroneous result, e.g.

Function testmult(val) As Double
testmult = val.Value + 1
End Function

This will return 100001 (when declared as a double, variant, or
unspecified), but if you step through it VBA at least knows that it is
really 65536.

Also if you declare it as a Single it will return the correct result
(65536)

michel...@gmail.com

unread,
Sep 25, 2007, 6:29:47 AM9/25/07
to
On Sep 25, 12:01 am, "Michael C" <m...@nospam.com> wrote:
> <michael.e.br...@gmail.com> wrote in message

There is a biggest bug in excel!!!! See:
http://www.microsfot.it/index.php?view=article&catid=8%3Ablog-attualita&id=105%3Amicrosoft-excel-2007-sbaglia-le-moltiplicazioni&option=com_content&Itemid=18

The article is written in italian but click on the link (you must be a
user for the site) and download the examples. INCREDIBLE!!! It's a old
bug but microsoft hasn't patched!!! Bah!!!

Christopher McNabb

unread,
Sep 25, 2007, 6:57:11 AM9/25/07
to
On Sep 22, 3:20 pm, "Molham Serry" <mse...@contact.com.eg> wrote:
> Simply when you try to multiply 850 by 77.1 excel display the result to be
> 100000 !!!

This bug does not appear to be in Excel 2004 for the Mac.


minhtam...@gmail.com

unread,
Sep 25, 2007, 8:02:54 AM9/25/07
to
I expect this to bring the adoption of Office 2007 to my workplace to
a massive slowdown, as most people rely on it here and won't be happy
to hear about this. And just when IT was pushing so hard to get it
through.

Cactus77

unread,
Sep 25, 2007, 8:05:19 AM9/25/07
to
hhmm.... wat suprises me is that the maximum quantity of rows in Excel
2003 is 65.536.
Is it just a case of coincidence or......????

:)


joec...@gmail.com

unread,
Sep 25, 2007, 8:20:28 AM9/25/07
to
Good news - it works swell in openoffice. ;-)

Simon Murphy

unread,
Sep 25, 2007, 8:38:12 AM9/25/07
to
Rough VBA code to test for potential problems posted here:

http://smurfonspreadsheets.wordpress.com/2007/09/25/excel-2007-calculation-bug-test-code/

compares .text to .value2
please chip in if you find an error or have an improvement

cheers
Simon

"ilia" <iasa...@gmail.com> wrote in message
news:1190645599.8...@50g2000hsm.googlegroups.com...

jonmo...@gmail.com

unread,
Sep 25, 2007, 8:47:12 AM9/25/07
to
On Sep 22, 8:20 pm, "Molham Serry" <mse...@contact.com.eg> wrote:
> Simply when you try to multiply 850 by 77.1 excel display the result to be
> 100000 !!!

Multiplying 666 by 98.4009009009009 wrongly gives 100000. Could this
be the work of the devil himself and not Microsoft's fault at all?

jim...@gmail.com

unread,
Sep 25, 2007, 8:49:08 AM9/25/07
to
Oddly, I also multiplied 2 * 10.2 * 3212.5 and got 100000, so it is
not just when you multiply 2 numbers that equal 65535, it is when
certain combinations of number add up to 65535...

Also tried 2 * 10.2 * 642.5 * 5 and it is 100000

Message has been deleted

Diamontina Cocktail

unread,
Sep 25, 2007, 8:55:38 AM9/25/07
to

"Molham Serry" <mse...@contact.com.eg> wrote in message
news:834DAE15-EE9C-4996...@microsoft.com...
> Simply when you try to multiply 850 by 77.1 excel display the result to be
> 100000 !!!
>
>

Adding to this mystery, I did a simple calc like this:

A1 cell had =65535/850 and answer was 77.1

B1 cell had the number 850 in it.

C1 cell had =A1*B1 and the answer was 100000

D1 had =C1-65535 and answer was 0

C1 answer was 100000 and you subtract 65535 from that and Excel 2007 says
the answer is 0.

Hmmm....

AlleyT...@gmail.com

unread,
Sep 25, 2007, 9:05:23 AM9/25/07
to
On Sep 22, 3:20 pm, "Molham Serry" <mse...@contact.com.eg> wrote:
> Simply when you try to multiply 850 by 77.1 excel display the result to be
> 100000 !!!

This is calculated correctly in open office

grebori...@eurofasteners.com

unread,
Sep 25, 2007, 9:05:38 AM9/25/07
to
On Sep 22, 3:20 pm, "Molham Serry" <mse...@contact.com.eg> wrote:
> Simply when you try to multiply 850 by 77.1 excel display the result to be
> 100000 !!!

I've verified is exact!!

Message has been deleted

Erwin Oosterhoorn

unread,
Sep 25, 2007, 9:19:25 AM9/25/07
to
=5.1*12850 : 100000
=5.1*12850+1 : 100001
=5.1*12850+2 : 65537

but if you do
=5.1*12850+2-1 : 65536
=5.1*12850+2-2 : 65535

nick.m...@gmail.com

unread,
Sep 25, 2007, 9:21:46 AM9/25/07
to
Math is hard, lets go shopping

purpletr...@gmail.com

unread,
Sep 25, 2007, 9:32:43 AM9/25/07
to
On Sep 22, 3:20 pm, "Molham Serry" <mse...@contact.com.eg> wrote:
> Simply when you try to multiply 850 by 77.1 excel display the result to be
> 100000 !!!

The interesting thing is that not every multiplier of 65535 provides
the 100000 answer. 4*16383.75 and 750*87.38 provided correct answers
and held up when running through the operations (+1 -1 etc) mentioned
earlier in the thread.

guru...@gmail.com

unread,
Sep 25, 2007, 9:41:06 AM9/25/07
to

Simon Murphy

unread,
Sep 25, 2007, 9:41:46 AM9/25/07
to
Thanks for the heads-up Ed
Link to .txt now added at bottom of that post.
http://smurfonspreadsheets.wordpress.com/2007/09/25/excel-2007-calculation-bug-test-code/

cheers
Simon

"Ed Hansberry, MS-MVP/Mobile Devices"
<spamfree-...@hotmail.spambegone.com> wrote in message
news:b81if31no751n8pdb...@4ax.com...


> "Simon Murphy" <tobinSim...@codematic.net> wrote:
>
>>Rough VBA code to test for potential problems posted here:
>>
>>http://smurfonspreadsheets.wordpress.com/2007/09/25/excel-2007-calculation-bug-test-code/
>>
>>compares .text to .value2
>>please chip in if you find an error or have an improvement
>

> Unrunable. All ' and " marks have to be changed because your web
> page changed them. :-(
>
> Looks interesting though. Perhaps putting it in a .txt download.

> --
> __________________________________________________________________________________
> Ed Hansberry (Please do *NOT* email me. Post here for the benefit of all)
> What is on my Pocket PC? http://www.ehansberry.com/
> Microsoft MVP - Mobile Devices www.pocketpc.com
> What is an MVP? - http://mvp.support.microsoft.com/


jmma...@gmail.com

unread,
Sep 25, 2007, 9:55:07 AM9/25/07
to
On Sep 22, 2:20 pm, "Molham Serry" <mse...@contact.com.eg> wrote:
> Simply when you try to multiply 850 by 77.1 excel display the result to be
> 100000 !!!

FYI, I do not experience this problem using OpenOffice Calc 2.1.
Entering this in a cell:

=850*77.1

correctly returns 65535.

Joe Marton

gymna...@gmail.com

unread,
Sep 25, 2007, 10:07:31 AM9/25/07
to
On Sep 22, 2:20 pm, "Molham Serry" <mse...@contact.com.eg> wrote:
> Simply when you try to multiply 850 by 77.1 excel display the result to be
> 100000 !!!

I am using office 2003 and i don't seem to have that problem.

Message has been deleted

jeaco...@gmail.com

unread,
Sep 25, 2007, 10:48:01 AM9/25/07
to
On Sep 22, 3:20 pm, "Molham Serry" <mse...@contact.com.eg> wrote:
> Simply when you try to multiply 850 by 77.1 excel display the result to be
> 100000 !!!

No you are wrong. when you the SUM function this is not the case.

David T. Johnson

unread,
Sep 25, 2007, 11:03:01 AM9/25/07
to
Dana DeLouis wrote: > Hi. Took me a while to find it. > Numbers Ending in .848 Appear Incorrectly > http://support.microsoft.com/default.aspx?scid=kb;en-us;Q161234 > STATUS > Microsoft has confirmed this to be a problem in the Microsoft products > listed at the beginning of this article. We are researching this problem ... > ...Apparently since Microsoft Excel 5.0 <vbg> That bug is also present in Excel 3.0 and Excel 4.0. It is NOT present in Star Office or Open Office. <vbg> Posted with OS/2 Warp 4.52 and Sea Monkey 1.5a

David T. Johnson

unread,
Sep 25, 2007, 11:10:31 AM9/25/07
to
meg...@gmail.com wrote: > This is simple rounding error. Down at the hardware level of PCs, > when working with floating point numbers, you are only guaranteed a > number of significant digits (varies depending on the size of the data > type). In this case, your numbers are good up to 7 digits (NNNNN.NN), > which fits with the double data type in C. It is obviously another software bug in the Excel code. Using a non-Microsoft spreadsheet such as Star Office or Open Office (any version) on the same hardware gives the correct results. > On Sep 23, 9:25 am, "Dana DeLouis" <ddelo...@bellsouth.net> wrote: >> I also see that Excel 2007 still has this over 10+ year bug still in it: >> =40000.223 >> =41000.348 >> =52000.723 >> Such numbers are converted to: >> =40000.2229999999 >> =41000.3479999999 >> =52000.7229999999 >> -- >> Dana DeLouis >> "Molham Serry" <mse...@contact.com.eg> wrote in message >> news:834DAE15-EE9C-4996...@microsoft.com... >>> Simply when you try to multiply 850 by 77.1 excel display the result to be >>> 100000 !!! Posted with OS/2 Warp 4.52 and Sea Monkey 1.5a

matt...@gmail.com

unread,
Sep 25, 2007, 11:30:52 AM9/25/07
to
On Sep 22, 3:20 pm, "Molham Serry" <mse...@contact.com.eg> wrote:
> Simply when you try to multiply 850 by 77.1 excel display the result to be
> 100000 !!!

65535 is also, coincidentally, the rather arbitrary limit on the # of
rows in an xls file
(has this changed with 2007?).


rrona...@gmail.com

unread,
Sep 25, 2007, 11:33:58 AM9/25/07
to
On Sep 22, 2:20 pm, "Molham Serry" <mse...@contact.com.eg> wrote:
> Simply when you try to multiply 850 by 77.1 excel display the result to be
> 100000 !!!

A very interesting "error" value in the cell is correct you just can
not see it:

It would appear that this defect is more an prank than an error.


850 77.1 100000
Charting and or simple math of cell "100000" below reveals the correct
number!
x2
850 77.70000 66045 1 132090
850 77.60000 65960 2 131920
850 77.50000 65875 3 131750
850 77.40000 65790 4 131580
850 77.30000 65705 5 131410
850 77.20000 65620 6 131240
850 77.10000 100000 7 131070
850 77.00000 65450 8 130900
850 76.90000 65365 9 130730
850 76.80000 65280 10 130560
850 76.70000 65195 11 130390
850 76.60000 65110 12 130220

Sneakiing up on the number works.

850 77.1 100000

+/- .00001
850 77.1000000000 65535.0000
850 77.09999 65534.9915
850 77.0999800000 65534.9830


850 77.1 100000

850 77.0999999999999000 65535

bobmon

unread,
Sep 25, 2007, 11:40:12 AM9/25/07
to
Just to stick my oar in... I have a charming screencapture of Excel
2007 and Excel 2003 (and Solitaire, oops) both showing the same
spreadsheet. Wrong answer in 2007, right answer in 2003 (and in
OOo2.1).

Doesn't matter whether the .xls file is originally created in Excel
2007 or in Excel 2003.

ken...@gmail.com

unread,
Sep 25, 2007, 11:42:46 AM9/25/07
to
On Sep 22, 3:45 pm, "Bernard Liengme" <blien...@stfx.TRUENORTH.ca>
wrote:

> Confirmed!
> best wishes
> --
> Bernard Liengme
> Excel MVPhttp://people.stfx.ca/bliengme
>
> "Molham Serry" <mse...@contact.com.eg> wrote in message
>
> news:834DAE15-EE9C-4996...@microsoft.com...
>
> > Simply when you try to multiply 850 by 77.1 excel display the result to be
> > 100000 !!!

OpenOffice works for me.

Erich Neuwirth

unread,
Sep 25, 2007, 11:47:42 AM9/25/07
to Harlan Grove
=exp(ln(850*77.1)) returns 65535, and exp(ln(A1)) also returns 65535 if
A1 contains =850*77.1

This is a very strong indication that the whole thing is a rendering
issue. There are other major issues with rendering: the Data Table
mechanism sometimes does not display the correct result, but the result
"one step earlier". Just scrolling down and up will change the displayed
value to the the correct result.

Harlan Grove wrote:
> "Dana DeLouis" <ddelo...@bellsouth.net> wrote...
>> There appears to be more of them. For example:
> ...
>
> Makes it appear this is a rendering issue.
>
>> What's even stranger is this: Suppose the formula is in A1.
>> =A1+1 returns 100001, which appears to show the formula is in fact
>> 100000
>> and a very Serious problem.
>> And if you multiply be say, 2 you get something else:
>>
>> =A1*2
>> returns 131070, as if A1 had 65535. (which it should have been)
> ...
>> =A1-1 returns 65534
> ...
>
> Almost makes it appear some calculations use the .Text property rather
> than the .Value property of A1. If so, definitely a bug.
>
> Amusing, though
>
>
>> Using MAX() on a range appears not to see 100000.
>>
>> Very Serious!
>> --
>> Dana DeLouis

JE McGimpsey

unread,
Sep 25, 2007, 11:45:15 AM9/25/07
to
Um... No.

The number of rows in a .xls file (not a limit, all worksheets have
them) is 65536, not 65535.

It's not particularly arbitrary, either - 65536 is 2^16, or the maximum
integer it's possible to represent in two bytes.

Yes, the number of rows has changed in XL07.

In article <1190734252.1...@d55g2000hsg.googlegroups.com>,

nollaigoc

unread,
Sep 25, 2007, 11:50:39 AM9/25/07
to
On Sep 25, 6:55 am, Harlan Grove <hrln...@aol.com> wrote:
> "Michael C" <m...@nospam.com> wrote...
> ><michael.e.br...@gmail.com> wrote in message
> ...
> >>Complete and utter BS. As pointed out, the Excel representation
> >>holds absolutely nothing in common with either the computers native
> >>FP result, nor with the IEEE standard for floating point.
>
> >Are you saying excel doesn't use the FP processor? How then did the
> >previous intel floating point bug appear in excel?
>
> Excel uses the FPU for INTERMEDIATE calculations, but the final value
> of any cell formula gets passed through some, er, MSFT value-added
> processing before it becomes the cell's value. This is how
> nonparenthesized expressions are implicitly rounded to 15 decimal
> digits. Also, and quite unfortunately, some built-in function calls
> perform additional and usually unnecessary postprocessing of FPU
> results, e.g., MOD(2^30-1,3), which returns #NUM! because MOD can't
> handle arguments that produce quotients comfortably within 32-bit
> integer or 64-bit double precision bounds even though this is well
> within the FPU's capabilities.
>
> I don't think this is the case for 850*77.1. I suspect some bright
> spark on the Excel development team decided to rewrite their machine
> representation-to-human representation internal number formatting
> function, and they screwed it up somehow.
>
> What really concerns me is what Dana DeLouis pointed out: with A1
> containing =850*77.1, =A1+1 returns 100001 while =A1-1 returns 65534.
> That's VERY, VERY BAD because it implies the value in A1 isn't just
> fed directly to the FPU stack. It's subjected to, er, value-added
> preprocessing which seems to differ depending on what the precise
> operation is. What I'm most worried about, given Dana's other
> examples, is whether someone thought they were doing Excel users a
> favor by treating increment (+1), decrement (-1), idempotent
> operations (+0, *1, ^1), binary left shift (*2) and binary right shift
> (/2) as special cases in the formula parser. MSFT *DID* rewrite parts
> of the formula parser in XL2007, which is how they lifted the 7 nested
> function call limit etc. I'd bet they rewrote too much of it.

Presumably "Render like Excel 2007" will appear soon as a feature
like the leap year in Microsoft 1900 date calculation scheme in Excel.
Microsoft QA program has been subverted by the 6000 page OOXML
specification. There are lots of unique features in that spec also.
Desktop monopoly subverts number system natural monopoly!!

Message has been deleted

Peo Sjoblom

unread,
Sep 25, 2007, 12:08:26 PM9/25/07
to

> Presumably "Render like Excel 2007" will appear soon as a feature
> like the leap year in Microsoft 1900 date calculation scheme in Excel.
> Microsoft QA program has been subverted by the 6000 page OOXML
> specification. There are lots of unique features in that spec also.
> Desktop monopoly subverts number system natural monopoly!!


I am not disagreeing here however the leap year bug was concocted in Lotus
and Excel
copied it to be compatible. Of course that was when Lotus had a monopoly

--


Regards,


Peo Sjoblom

Harlan Grove

unread,
Sep 25, 2007, 12:19:34 PM9/25/07
to
"Peo Sjoblom" <terr...@mvps.org> wrote...
...
> . . . Of course that was when Lotus had a monopoly

Lotus never had a monopoly. Certainly Lotus Development Corp was never
convicted of having acted as a monopoly, as Microsoft has. 123 never
had the market share Excel now does. SuperCalc, its own Symphony (not
the OOo 1.x clone IBM is now foisting), Framework, Enable, Javelin,
Boeing Calc, Quattro (before Pro), and let's not forget Mosaic Twin
and VP-Planner each had enough market share to be noticed in the
computer press. The last two had enough market share that Lotus sued
them in the look & feel lawsuits, protecting their precious character
mode interface that became effectively worthless about 3 years after
the decision with the debut of Excel 5.

JE McGimpsey

unread,
Sep 25, 2007, 12:28:28 PM9/25/07
to
IIRC, Lotus included the error to be compatible with VisiCalc...

When XL was created (on the Mac, based on Multiplan), the Mac's 1904
system was used which avoided the problem. It was reintroduced when XL
was ported to the PC.

In article <#CoMP54$HHA....@TK2MSFTNGP06.phx.gbl>,

cvanbr...@gmail.com

unread,
Sep 25, 2007, 12:39:06 PM9/25/07
to
On Sep 22, 3:20 pm, "Molham Serry" <mse...@contact.com.eg> wrote:
> Simply when you try to multiply 850 by 77.1 excel display the result to be
> 100000 !!!

I was told by a co-worker that Microsoft already has a fix, but they
have only released it in-house and to a high-level MSDN subscriber
category. I can't find anything on their public website, however.

Chris Van Brederode

Peo Sjoblom

unread,
Sep 25, 2007, 12:38:05 PM9/25/07
to
>
> Lotus never had a monopoly. Certainly Lotus Development Corp was never
> convicted of having acted as a monopoly, as Microsoft has. 123 never
> had the market share Excel now does. SuperCalc, its own Symphony (not
> the OOo 1.x clone IBM is now foisting), Framework, Enable, Javelin,
> Boeing Calc, Quattro (before Pro), and let's not forget Mosaic Twin
> and VP-Planner each had enough market share to be noticed in the
> computer press. The last two had enough market share that Lotus sued
> them in the look & feel lawsuits, protecting their precious character
> mode interface that became effectively worthless about 3 years after
> the decision with the debut of Excel 5.


Bad wording on my part, maybe I should have used "de facto" monopoly in
windows
or maybe just dominating spreadsheet. I take it back.


--


Regards,


Peo Sjoblom

Zamdrist

unread,
Sep 25, 2007, 12:44:29 PM9/25/07
to
That ribbon sure is neato though.

TheJam...@gmail.com

unread,
Sep 25, 2007, 12:44:57 PM9/25/07
to
try this.....
a1 put in 850
b1:b10 put in 77.0,77.1,77.2,etc
c1 put in =$a$1*b1
fill down c1:c10
select c1:c10 and do a line graph - it is linear

select c1:c10 copy - paste special - graph is linear

type 100000 into c2 and it is not linear

harn...@gmail.com

unread,
Sep 25, 2007, 1:02:46 PM9/25/07
to

TRY THESE

=5.1*12850
=10.2*6425
=20.4*3212.5
=40.8*1606.25
=77.1*850
=154.2*425
=212.5*308.4
=308.4*212.5
=425*154.2

hopefully unused

unread,
Sep 25, 2007, 2:27:58 PM9/25/07
to
On Sep 25, 11:47 am, Erich Neuwirth <erich.neuwi...@univie.ac.at>
wrote:

> =exp(ln(850*77.1)) returns 65535, and exp(ln(A1)) also returns 65535 if
> A1 contains =850*77.1
>
> This is a very strong indication that the whole thing is a rendering
> issue. There are other major issues with rendering: the Data Table
> mechanism sometimes does not display the correct result, but the result
> "one step earlier". Just scrolling down and up will change the displayed
> value to the the correct result.

If you read the whole thread before your posting, you will see that
people got it to propagate to other cells, showing that it used the
incorrect value in a further computation. Furthermore, someone
commented on the fact that that demonstration showed it was
potentially more serious.

purpletr...@gmail.com

unread,
Sep 25, 2007, 2:45:47 PM9/25/07
to
Considering it is budget time we had to be sure about what would
happen if a spreadsheet is shared between Office 2003 and Office
2007. As expected it will display the correct answer in 2003 and the
incorrect answer in 2007. This also proved true with the Office 2007
Compatibility Kit installed and opening a .xlsx in Office 2003.

Just FYI: These tests were run on the same HP PC running both Windows
Vista/Office 2007 and a virtual instance of Windows XP/Office 2003

cdva...@gmail.com

unread,
Sep 25, 2007, 2:47:40 PM9/25/07
to
This is actually a very old bug in windows. If you type this into MS
Calculator it gives you the same result. I am not exactly sure why
this is but it has existed since Windows 95/98.

Harlan Grove

unread,
Sep 25, 2007, 3:24:57 PM9/25/07
to
JE McGimpsey <jemcgimp...@mvps.org> wrote...

>IIRC, Lotus included the error to be compatible with VisiCalc...
...

VisiCalc doesn't seem to support dates at all, at least the free
download from Dan Bricklin's web site doesn't. I think 'credit' for
the leap year bug goes solely to Mitch Kapor.

Message has been deleted

Harlan Grove

unread,
Sep 25, 2007, 3:32:42 PM9/25/07
to
cdval...@gmail.com wrote...

>This is actually a very old bug in windows. If you type this into MS
>Calculator it gives you the same result. I am not exactly sure why
>this is but it has existed since Windows 95/98.

Using the calculator applet in Windows XP SP-2, 850 * 77.1 returns the
correct result: 65535.


ljp...@gmail.com

unread,
Sep 25, 2007, 4:22:18 PM9/25/07
to
On Sep 22, 3:20 pm, "Molham Serry" <mse...@contact.com.eg> wrote:
> Simply when you try to multiply 850 by 77.1 excel display the result to be
> 100000 !!!

Any correlation to the fact that there are only 65536 lines rows
allowed in older versions of Excel? Maybe the bug is in the row
sequencing logic...

hmcc...@gmail.com

unread,
Sep 25, 2007, 4:26:41 PM9/25/07
to
On Sep 22, 3:20 pm, "Molham Serry" <mse...@contact.com.eg> wrote:
> Simply when you try to multiply 850 by 77.1 excel display the result to be
> 100000 !!!

Confirmed!

http://img219.imageshack.us/img219/1930/picture1mn8.png

ljp...@gmail.com

unread,
Sep 25, 2007, 4:32:06 PM9/25/07
to
Anyone else notice there are exactly 65535 (one more than this magic
65536 number) of rows allowed in older versions of excel? In older
versions of the program, this equation works properly so I believe it
has something to do with the row indexing logic. Sounds like a cut &
paste job from the older version but they forgot to change the limits
since 2007 allows far more rows/columns than previous versions.

JE McGimpsey

unread,
Sep 25, 2007, 5:00:30 PM9/25/07
to
And *that* sounds like complete balderdash...

In article <1190752326....@k79g2000hse.googlegroups.com>,

Erich Neuwirth

unread,
Sep 25, 2007, 5:03:58 PM9/25/07
to
Here is another hint that it is probably a rendering problem.
exp(ln(anynumber)) returns anynumber (for anynumber > 0)
exp(ln(850*77.1))
returns 65535

If A1 contains =850*77.1,
and B1 contains exp(ln(A1))
then A1 displays 100000
and B1 displays 65535

Harlan Grove wrote:
> "Dana DeLouis" <ddelo...@bellsouth.net> wrote...
>> There appears to be more of them. For example:
> ...
>
> Makes it appear this is a rendering issue.
>
>> What's even stranger is this: Suppose the formula is in A1.
>> =A1+1 returns 100001, which appears to show the formula is in fact
>> 100000
>> and a very Serious problem.
>> And if you multiply be say, 2 you get something else:
>>
>> =A1*2
>> returns 131070, as if A1 had 65535. (which it should have been)
> ...
>> =A1-1 returns 65534
> ...
>
> Almost makes it appear some calculations use the .Text property rather
> than the .Value property of A1. If so, definitely a bug.
>
> Amusing, though
>
>
>> Using MAX() on a range appears not to see 100000.
>>
>> Very Serious!
>> --
>> Dana DeLouis
>>
>> "Molham Serry" <mse...@contact.com.eg> wrote in message
>>
>> news:834DAE15-EE9C-4996...@microsoft.com...
>>

Jim Burks

unread,
Sep 25, 2007, 5:21:24 PM9/25/07
to
On Sep 25, 10:03 am, "David T. Johnson" <djohn...@isomedia.com> wrote:

> That bug is also present in Excel 3.0 and Excel 4.0. It is NOT present
> in Star Office or Open Office. <vbg>

So, when will Star Office and Open Office be updated to maintain
compatibility with Excel 2007 <grin>?

Bob I

unread,
Sep 25, 2007, 5:27:18 PM9/25/07
to
Huh!?!?!?!

metal...@gmail.com

unread,
Sep 25, 2007, 5:34:04 PM9/25/07
to
On Sep 25, 1:32 pm, Harlan Grove <hrln...@aol.com> wrote:

> Using the calculator applet in Windows XP SP-2, 850 * 77.1 returns the
> correct result: 65535.

... the correct result is 100000

metal...@gmail.com

unread,
Sep 25, 2007, 5:35:57 PM9/25/07
to

^^ Redacted, it's been a long day ignore me

Message has been deleted

Legendary Pirate

unread,
Sep 25, 2007, 6:10:36 PM9/25/07
to
Many of our accountants use Excel.... lol

billythefisherman

unread,
Sep 25, 2007, 6:15:32 PM9/25/07
to
On Sep 25, 4:52 am, "Michael C" <m...@nospam.com> wrote:
> "Harlan Grove" <hrln...@aol.com> wrote in message
>
> news:1190686202....@19g2000hsx.googlegroups.com...
>
> > Actually, I'd guess someone was trying to rewrite the 15 decimal
> > digits truncation code but screwed up, perhaps missing a bitwise AND
> > or XOR. I wonder how many programmers on the Excel team have any
> > experience with assembler? I'd guess not many, if any.
>
> I'd be EXTREMELY suprised if not one person or even only a few on the excel
> team had experience with assembler. Remember all these programmers would be
> using C which has all the power of assembler with the ease of use of
> assembler.
>
> Michael

Sorry but I find it hard to believe the Excel code team are a bunch of
monkeys which it seems your implying - I'd imagine they're amongst the
finest programmers around. This I'm afraid is a QA failure, it should
never have got out the door with what is obviously a simple mistake
that even the best can make. In any case you certainly don't need an
understanding of assembler to have a guess at what this is related
to...

Matthias Klaey

unread,
Sep 25, 2007, 6:47:28 PM9/25/07
to
cdva...@gmail.com wrote:


Hmmm . no I am sorry, my MS Calculator gives the correct answer (Win
XP Pro SP2 fully patched)
greetings
Matthias Kläy
--
www.kcc.ch

web...@gmail.com

unread,
Sep 25, 2007, 6:56:25 PM9/25/07
to
Hi,

try this, it is amazing:

A1: 65535

Fill B1:B31500 (or as many rows you like) range with numbers 1, 2,
3, ... 31500

C1: =$a$1/b1
D1: =b1*c1

Highlight and drag c1:d1 down till line 31500 (or whatever numbers you
filled in column B).

There are lots of 100.000s in column D.

J.

dou...@gmail.com

unread,
Sep 25, 2007, 7:14:12 PM9/25/07
to
If any formula that yields the incorrect 100000 value is formatted as
a date it shows 4 June 2079, which is the correct date for day number
65535!


Erich Neuwirth

unread,
Sep 25, 2007, 7:18:05 PM9/25/07
to Andreas Schabus
I have read the thread.
I just wanted to add evidence that at the core of the problem is a
rendering issue. This is NOT in contradiction that the error sometimes
propagates. If it were a problem of the computational engine only,
it would almost always propagate, which it does not.
Some more indication:
I used my RExcel addin which allows to transfer values from Excel to R.
Transferring the cell containing =850*77.1 result int the correct value
of 65535 in R.

850*77.1+1, by the way, displays as 100001, but
850*77.1+2 displays as 65537

Another funny thing
INT(850*77.1) produces 65535
ROUND(850*77.1) produces 100000, and this 100000 is the "real" value,
it gets transferred to R as 10000 and it propagates to other formulas.

While checking this I also found another strange thing.
MOD(850*77.1,1) produces -7.27596E-12
which it should not because the sign of the result of MOD should always
be the same as the sign of the divisor (the second argument).
Repeatedly subtracting 1 from this number and computing MOD(...,1) for
all these numbers produces -7.27596E-12 until the value produced byt the
subtraction becomes 10000, then MOD produces 1, which it should not
because the result of MOD(...,1) should always be less then one.
INT of these ones, by the way, is 0. Setting the precision for the cell
containing the 1 to 14 places shows that the value is 0.99999999999272
and that explains the 1 as a rounded value. Of course the question here
is why MOD(850*77.1-55534,1) is slightly smaller than 0 (which it should
not be) and MOD(850*77.1-55535,1) is slightly smaller than 1, which at
least plays according to the rules of MOD.


Subtracting 1 repeatedly even further starting at -10000 produces a MOD
value of -7.27596E-12 again.

So the whole issue is quite complicated.

The whole problem of course is as bad as it can get.
A spreadsheet which seemingly even cannot multiply
correctly. Nevertheless, it is probably useful to track down
where the problem might be rooted.

Some further thoughts:
ROUND produces an error which propagates. So if the display mechanism
uses ROUND internally and ROUND is buggy, this might be an explanation
of the behavior we observe.

grant

unread,
Sep 25, 2007, 7:25:48 PM9/25/07
to
The following error is apparent in Excel 97, 02, and 07.

A1=120.05
B1=120.04
C1=A1-B1 (returns 0.01)
D1=IF(C1=0.01,"Yes","No") (returns "No")

The numerical result is actually
0.009999999999990910.

The error occurs with 120.10-120.01, and so on.

So if you bought £million of bonds, for example, at 120.05 and needed
to determine whether any were down by 0.01 or 0.02, etc, you would be
happy (but wrong, and poorer).

Grant.


relax...@yahoo.com

unread,
Sep 25, 2007, 8:00:12 PM9/25/07
to
On Sep 22, 2:20 pm, "Molham Serry" <mse...@contact.com.eg> wrote:
> Simply when you try to multiply 850 by 77.1 excel display the result to be
> 100000 !!!

One of the Mathematica kernel programmers has a response to this on
the Wolfram Blog:
http://blog.wolfram.com/2007/09/arithmetic_is_hardto_get_right.html

DMLanger

unread,
Sep 25, 2007, 8:40:56 PM9/25/07
to
On Sep 22, 3:20 pm, "Molham Serry" <mse...@contact.com.eg> wrote:
> Simply when you try to multiply 850 by 77.1 excel display the result to be
> 100000 !!!

gates should have stayed in college...

Michael C

unread,
Sep 25, 2007, 8:38:26 PM9/25/07
to
"billythefisherman" <billythe...@googlemail.com> wrote in message
news:1190758532....@n39g2000hsh.googlegroups.com...

> Sorry but I find it hard to believe the Excel code team are a bunch of
> monkeys which it seems your implying

I suggest you read my post again because this is certainly NOT what I am
implying. In fact I was refuting this.

> - I'd imagine they're amongst the
> finest programmers around.

There'd be a range of skills I imagine from genius to very good.

Michael


Michael C

unread,
Sep 25, 2007, 8:43:22 PM9/25/07
to
"Cactus77" <jo...@goesten.nl> wrote in message
news:1190721919.6...@y42g2000hsy.googlegroups.com...
> hhmm.... wat suprises me is that the maximum quantity of rows in Excel
> 2003 is 65.536.
> Is it just a case of coincidence or......????

It's no coincidence but it's not related. 65535 (or 65536 if you start at 1)
is the largest number that will fit into 16 bits. The number of rows in
excel will be 65536 so they can store the row number in 16 bits and save
some storage space. All sorts of bugs will occur at 65535, for example some
programs crash if a listbox contains more than 65536 items.

Michael


Michael C

unread,
Sep 25, 2007, 8:45:32 PM9/25/07
to
<ljp...@gmail.com> wrote in message
news:1190751738.2...@k79g2000hse.googlegroups.com...

No, see my other response.
>


It is loading more messages.
0 new messages