Check those excel spreadsheets ;)

General Computing Microsoft Windows
1

Snippet from Hexus…

850x77.1= erm, gimme a second… er, 100,000? Yeah, that’ll do.
Advertisement
It’s a given that every major software release, being the complex things that they are, has a few bugs here and there… but this multiplication bug in Excel 2007 is a pretty big oversight on Microsoft’s part.

On a Google.groups message board, a poster has found a bug in Excel where anything that should resolve to 65,535 is displayed as 100,000.

In trying, and succeeding, to confirm this, other posters have discovered more flaws in Excel 2007’s multiplication abilities, such as the following:

=5.112850
=10.26425
=20.43212.5
=40.81606.25
=77.1850
=154.2425
=212.5308.4
=308.4212.5
=425*154.2

All of these give incorrect answers… defaulting, it would appear, to 100,000.

Worse, is the formula is contained in a cell and then used as part of another formula, the result from that will be wrong as well as the original formula solution will be incorrect. Good, eh?

I’ve just spoken with one team of accountants using Excel 2007 and they’re now checking all their formulas as this could have a major impact on their clients’ spreadsheets.

The bug has been reported to Microsoft so you can expect a fix pretty damn soon… until then, keep that Texas Instruments calculator handy!

2

How could they let that slip by??

3

Crikey. Thanks for that PMM. The cost to business could be staggering.
Me, I’m still using XL2000 :wink:

4

It looks like it is only the display, at first. If I use the first example (5.1*12850) in A1 and put =A1-1 in B1, B1 does display 65534.

I take that back: A1+1 displays 100001 :eek: However, adding 1.1 does give 65536.1, and adding 1.1 to the 100001 (A1+1) gives 65537.1 as expected.

This could take some working out.

5

Right, here we go:


A		B		C	D	E	F	G	H
Formula		=425*154.2	=B2+1	=B2+2	=B2-2	=B2+1.1	=C2+1	=C2-1
Result		100000		100001	65537	65533	65536.1	65537	100000
Expected	65535		65536	65537	65533	65536.1	65537	65535
=IF(B#=C#)	TRUE		TRUE	TRUE	TRUE	TRUE	TRUE	TRUE
Date		040679		050679	060679	020679	050679	060679	040679

It does seem to mostly affect only the display. Although derived formulae do occasionally show incorrectly the internal values still seem to be correct. You could just display it as a date which does display correctly :wink: