Bug in Excel 2007

[Ed Hansberry, MS-MVP/Mobile Devices]

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.

cheers
Simon

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

 

[Diamontina Cocktail]

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

 

[AlleyTrotter]

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

This is calculated correctly in open office

 

[greboriorobert]

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

I've verified is exact!!

 

[mwt]

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

Try this
A B C D
5.1 12850 0 =($B$1*$A$1)+C1 --> 10000
1 =($B$1*$A$1)+C2 --> 10001
-1 =($B$1*$A$1)+C3 --> 65534
2 =($B$1*$A$1)+C4 --> 65537

 

[Erwin Oosterhoorn]

=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.martini]

Math is hard, lets go shopping

 

[purpletravelgrrl]

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.

 

[guru4vedi]

Confirmed!!

http://www.gangarasa.com/images/excel-bug.PNG

 

[Simon Murphy]

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"

 

[jmmarton]

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

 

[gymnast1717]

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.

 

[Ed Hansberry, MS-MVP/Mobile Devices]

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/

I tried this on a large worksheet and I get Error 6 (Overflow)

It was on formula 10,924 when it happened. It reported thousands
of errors, but that is because the worksheet is formatted to show
rounded thousands, so virtually everything was reported as an
error. I think you'll have to take a different approach than
trying to figure out what the formula result looks like.

cheers
Simon

"Ed Hansberry, MS-MVP/Mobile Devices"

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

 

[jeacobacci]

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]

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>

 

[David T. Johnson]

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.

 

[mattinger]

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?).

 

[rronan.rsu]

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]

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.

 

[kenk16]

Confirmed!
best wishes

OpenOffice works for me.