What's 77.1 x 850? Don't ask Excel 2007

A Microsoft manager has confirmed the existence of a serious bug that could give programmers and number crunchers a failing grade when relying on the latest version of Excel to do basic arithmetic. The flaw presents itself when multiplying two numbers whose product equals 65,535. Fire up your favorite calculator and multiply …

[Nano nano]

Not only but also

Google calculator refuses to tell you the result of 9 divided by 11, unless you enclose the sum in brackets, although it works for all other numerators ...

[this]

Northern Rock

Maybe this explains the recent Northern Rock debacle?

After all, if a spreadsheet says so, it MUST BE TRUE - and accountant/bookeeper types just love their spreadsheets...I know, I've met some.

[Jeff Carr]

Obvuscation is the game

Surely the reg could connect the dots with the other nefarious actions take by the redmond devils to impair compatibility with openoffice? This "calculation logic" error is an interesting look into just how much effort is being exhausted to game the legal system and standards groups into believing they shouldn't look behind the curtain.

[Anonymous Coward]

Noooooo!

Our business areas already confirm apr calculations, done by custom built high end maths software, using an excel spreadsheet put together by the biggest numpty they can find. For some reason they always believe Excel is right when the answers don't match.

Now its just going to get stupid when an answer that might actually be correct is displayed incorrectly.

[Will Godfrey]

Not Unique

Some versions of Sage will occasionally display one invoice total on-screen, but send a different value to the printer. Drives our office people nuts, but there seems to be no discernible pattern :(

[Anonymous Coward]

This is bad

When I think of all the spreadsheets, macros, and nested tables being used by pharmaceutical companies, civil engineers, capacity planners, design engineers, accountants, and scientists I find it very difficult not to come to the conclusion this is bad. Really bad. Something probably already happened or is happening that we dont even know about based on this issue. That is if there were only a single bug...multiply that by that many...and there is big trouble out there! I cant tell you how many applications I use where I have macros or sheets that I dont even see, dont have the time to look at, could not possibly scrutinize to make some pretty serious decisions in a quick manner that is out there. You may reply or think that is just foolhardy but let me remind you that the whole purpose of technology and programs like excel are to save manpower and ensure exactness. This is bad.

[John Stag]

OOXML version...

<ComputeLikeExcel2007>

77.1 x 850

</ComputeLikeExcel2007>

[Don Mitchell]

@Jeff

You think an embarassing bug is introduced on purpose by MSFT to thwart Open Office? That makes no sense, but then conspiracy theories rarely make sense.

I've been writing a book, and I recently tested the typography quality of several products (Word, Quark XPress, InDesign). I decided to try OpenOffice too, thinking perhaps they were using the cool typography engine from LaTex. Much to my surprise OO output *exactly* the same layout and hyphenation as Word! Just a reverse-engineered copy of the commercial product, which is pathetic. On top of that, OO takes up more memory and runs slower (see the benchmarks run by ZDNET recently).

The OpenOffice guys better get to work and include this arithmetic bug, to maintain perfect replication of MS Office features.

[Anonymous Coward]

I keep getting

a suffusion of yellow for 77.1x850 must be a pirated copy.

[Paul]

Err, the MS bloke is wrong

While multiplication of a cell that results in 65535 will use the right value, addition uses the wrong value.

Try this:

in A1: "=77.1*850

in A2: "=A1+1"

in A3: "=A1*2"

A1 will show 100,000

A2 will show 100,001

A3 will show 131,070

Only A3 is correct. Addition clearly uses the "result" from A1, while the evaluation of A3 folds in the formula in A1, presumably to avoiding cumulative rounding errors.

Paul

[Fran Taylor]

Go, Microsoft!

Typical results for yet another Microsoft math bug:

- they have a large hole in their QA testing

- leaves many users scratching their heads until they figure it out

- world + dog writes their regression test for them

- they make a quick fix without addressing the systemic problem of not doing proper math testing

- they have been making mistakes just like this one for years and you'd think by now that they'd actually test the math in their products.

[Andy Bright]

A small white lie i feel

You know very well why the display is 100,000. The fact that's the highest value that can be represented by an unsigned 16 bit binary number is merely a red herring.

The long and short of it is you've probably accidentally installed the Enron Accounting macros.

[Sandy Scott]

RE: Err, the MS bloke is wrong

Not exactly, if you test it a little further, and put in A4 ="A2 *2" you get 131,072, which actually shows that excel is using a fairly neat shortcut to reduce the display time, by only modifying the relavant digit when it needs to, but recognises that most other operations will modify all of the digits, so those results are calculated the slow way, by working up the formula tree.

[Joe Knappett]

Most companies are still using XP and 2003

The vast majority of corporate users (including the local authority I work for) are at best poking at Vista and 2007 gingerly on a test rig somewhere.

They can't afford to have apps fall over, and Office 2007 isn't offering any must-haves that 2003 doesn't beat because of stability.

[Anonymous Coward]

no warantee

You read the licence, right? That bit where it says "this software is not guaranteed fit for purpose and should not be used for anything at all, ever."?

MS 1 - The World nil

:(

[Andy Barber]

I still can't...

... see the Paris Hilton link!

[Anonymous Coward]

Good way to calculate my pay rise

I'll remember this when it comes to my pay review.

Mind you from the other angle, who wants to take the case to Trading Standards.

The product is obviously unfit for the purpose it is meant to be used for.

[Dann]

More fun with numbers

For a bit of fun I had a play.

A1 77.1*850 = 100000

A2 A1-1 = 65534

A3 A2+2 = 100001

so that means its an error for 65535 and 65536 but not 65534 or 65537

Just to prove it try

77.1*850 = 100000 and 77.1*850+1 = 100001

Oh well.

[Anonymous Coward]

@This is bad

If it is a nested table it won't be an issue as anything referencing the affected cell will use the correct value. It is only when displaying it that there is a problem. So unless the entire nested system came to the RESULT as 65535 and then someone wrote it down or manually typed it into another spreadsheet it won't go any further.

[Anonymous Coward]

Fix due soon

"The fix is due in around 47 million days, give or take a day" said a Microsoft spokesman yesterday, quoting timescales from an internal bug-tracking spreadsheet.

[baka]

Excel is also limited to 65536 rows.... conspiracy?

My Analyst wife reminded me that Excel also has a maximum of 65536 rows.... sounds like MS really has an issue with this number and hose surrounding it....

[The Aussie Paradox]

Microsoft redefining calculations?

<quote> "He stressed that the bug, which was introduced when Microsoft made changes to the Excel calculation logic..." </quote>

Hmmmm, are MS trying tell us that Mathamatics have been redefined to suit their needs?

[Uwe Dippel]

New measure

I suggest ElReg adds this as unit for accuracy measurements.

Something like an error of 65535 to be 1 Excel, or the difference 100000-65535 to equal one Excel?

May I asked my learned collegues for your input, please ?

[David]

Another probem with Excel.

MS Office costs something like $300, and Open Office, Google docs and IBM Symphony all costs $0 and they support ODF too.

It is also a phenomenon why people still buy MS Office and Excel.

It appears that they main types are idiots with money to burn.

[Dave]

talking of cumulative rounding errors....

python 2.4.4 gives the answer

>>> 77.1 * 850.0

65534.999999999993

or maybe excel '07 is the reason the financial markets are in trouble. it's not sub-prime lending, its sub-prime software.

[call me scruffy]

@Sandy Scott,

As Per Dan's post Excell gets it wrong for 65536 too!

I'm sort of relieved, the idea of diffing the output of sprintf and then working out how much of the rendered contents of one cell could be blittered into another made me pretty ill!

Hopefully that's one of the "six" misrepresented numbers, doesn't

six strike anyone else as a fricking weird number of failiures?

[Tom Simnett]

Hat, Coat, and Taxi for Microsoft

I really do think that maybe they should call this one themselves this time!

[Gleb]

@This is bad

The scientific community I personally belong to does NOT use Excel. In fact, we don't use spreadsheets, windows or practically any microsoft products (There are a bunch of windows computers & macs, for the daft... ). For simple computing you can use Mathematica, Matlab och Maple. Something more advanced you code yourself. I'm not even sure what Excel can do, but for some reason I don't think I'm missing much.

Now, you might think that we are a minority; but I'm speaking for the largest polytech in Sweden. Oh and yeah, our systems are interconnected with at least a few other major universities...

[John]

That's it...

..I now want to see the following signs on Bridges, Aircraft, Tall Buildings and Motor Vehicles. etc.

"WARNING: The design process for this structure/vehicle/device involved calculations made by Excel 2007"

We need to know.

[fixit_f]

Software vs Hardware

Despite only being 28 years old, I remember two things worth factoring in:

1) Usborne books when I was a kid assuring me that "computers don't make human mistakes, that's why they're the ultimate calculating machines.

2) The early incarnations of the Intel Pentium processor that had bugs in floating point arithmetic which made calculations (even in whatever version of excel was around at the time) cock up under certain very rare circumstances. I mean seriously, the computer mags of the time (there was precious little internet then) would give you examples of how you could trip it up, but it was accepted wisdom that unless you were some sort of hardcore science boffin you'd never notice it.)

So as a relatively young IT professional and also as a genuinely experienced and platform-agnostic fella (I'm talking Vista and Linux back to Pick) why am I the only bugger who thinks:

"Sod it, in a new and complex and recently released product this is essentially a teething problem and is pretty much to be expected. Even in a formally released product. So long as they fix it quick-ish in a service pack then all is good"

If Open Office did this people would just write it off as a bug. Just because it's MickeySoft, much as I'm wary of them, "sod it" it's nothing more than an unfortunate oversight. Even the finest ofdevelopers are prone to a scenario that they didn't test appearing as a bug in their software. Big deal, at least you can patch it rather than it being an inherent hardware bug like the floating point stuff in the earlier pentiums. I work for an investment bank where lots of stuff among the traders is still done through excel, and while there are lots of unfixed bugs that they still work around they are still as a whole grateful for such a powerful package that can calculate stuff on the fly like excel does. Much as I dislike Microsoft's OS, the Office Suite is almost beyond reproach - it's excellent.

[J]

Re: Software vs Hardware

"Sod it, in a new and complex and recently released product this is essentially a teething problem and is pretty much to be expected."

Er, I hope you are the only one thinking so, really. I mean, it's a bloody software whose only purpose in life is basically making calculations, no matter what other uses people put it to (even writing stupid games, or so I've heard)... And it makes a mistake my free calculator does not? What has to be "sodded" here is whomever wrote/tested Excel, really.

[Geraint]

Re: This is bad

Fear not. Real engineers would never use Excel for any calculations.

[fixit_f]

Furry Muff

J, I take your comment on board and in most ways I agree, apart from the aggressive and slightly substandard intelligence / illiterate manner in which you raised it "It's a bloody software" is a case in point. Mate if you've seen something do the job better let me know, I'd like to see it because I'm as anti-ms as you can get, It's pretty poor that a package whose entire raison d'etre is calculating numbers should go so fundamentally wrong, but once you've spent time in software development around allegedly "critical systems" in finance and seen them do obvious numeric f**k ups - erm, you'll understand the sloppiness that is de facto throughout the industry, Seriously, you wouldn't believe the state of "mission critical" systems in financial services. I'd happily write a book on it (if I didn't work in it and expect an expose to cost me my job) - Northern Rock anyone?

[Adrian Esdaile]

Bug found in software! Pictures at 11!

Yawn.

YEARS ago, Autodesk beat MS to the game with some doozy calc bug in Autocad - like 10m x10m giving 100.001m^2 (and who knows what it would do if you used cubits or OSPs). Bug caused by faulty internal conversions - you entered metric, Acad internally converted to imperial (EVIL!), rounded, did the math, rounded, converted back to metric, rounded. Result the bigger the dimension strings, the bigger the error!

Not many people noticed, but I had to convert to chinese MU(area) kept getting wierd errors.

Lucky no-one ever used Acad to design bridges for nuclear reactors! Hang on....

[Daniel Ballado-Torres]

bits and bytes

"Yeah, it's 2^16-1 (or just plain -1 in 16-bit), but why on earth does that matter?"

It is also the max row number (65,536) in Excel. I'd say it is well... kind of curious that happens, it might mean your "floating point" ops are being done in 16-bit int operations.

While most of the world+dog has moved to 32-bit and 64-bit long ago. Not all decimal ops are done as floating ops, because of the potential of misrepresentation (do a small C program, store .2, printf the value, you'll see what I mean) so many financial software and databases use the concept of a "fixed-point datatype" that is nothing more than an integer with a "decimal point" fixed on a qty of digits. So say, the int is 496043, but set with 2 decimal points, the actual number is 4960.43

Now I wonder why would 2^16-1 show 100,000 ... looks like I'm better off doing my own software and using BigInteger for calculations. ;)

[MacroRodent]

@ Don Mitchell

"Much to my surprise OO output *exactly* the same layout and hyphenation as Word!"

No reason to be surprised: If OO did it differently, a lot of users accustomed to using MS Word would call it a bug. (In fact, early OO versions _did_ differ a lot from the MS layout). Keeping the layout is also the only way to try to ensure page numbers don't jump around when a document is transferred between the programs. This is just another case of Microsoft effectively establishing a sub-optimal industry standard.

Bu I agree about the ugliness of memory usage in OO (not that it is any serious problem on modern PC:s that routinely have 512Mb or more of RAM).

[Doc]

@Fix due soon @we need to know

RATFLMFAO

Aussie Doc

[bluesxman]

RE: Not only but also

"Google calculator refuses to tell you the result of 9 divided by 11, unless you enclose the sum in brackets, although it works for all other numerators ..."

Utter balderdash, tosh, bunkum and piffle ... a quick Google of "9 / 11" (sans quotes) returns the result. OK, I grant you, if one drops the spaces it pre-empts your intentions (and its normal parsing routines, which disregard the slash in searches) and decides that you really meant to ask it about the 11th of September (2001) -- I'm going to go out on a limb here and propose that that's actually a feature rather than a bug. :oP

[Bloody_Yank]

RE - Big Trouble .....

Actually sometimes engineers do use excel ----- this was on of the tools of choice for statistical analysis of data for PC board design - high speed circuit design at Intel from 2002 until I left a few years later ..... to be clear we we using many highend design tools - but the gobs of data would go right into Excel for analysis.

But really if you're designing a bridge or an airplane you wouldn't use Excel would you ..... I mean thats like REAL engineering

[Tom Cooke]

"Nobody yet uses Office 2007"?

Erm... We have moved to Excel 2007 pretty much only because we can have more columns; we have planning software that stores more values per calendar period than you can shake a stick at, and we needed more values and more calendar periods... Now if only Microsoft would release a version of Access that supports >2GB in a single file, my evil empire would be complete, mwahahaha.

[David Page]

Here we go, then: 10, 9, 8, 6, 5...

I can't be the only one thinking of Holly from Red Dwarf who had a blind spot for sevens.

[Phill]

More foul play a foot me thinks

As usual Microsoft embrace the 'mathematics' standard, extend it with new 'answers' and extinguish it's the usability.

[Jack]

@Andy

Andy Barber wrote:

"...see the Paris Hilton link!"

Its clear. She can't count either.

[Anonymous Coward]

Pay raise.

Damn, so my pay rise wasn't for good performance then! Why couldn't Microsoft have covered this one up and delayed a fix for two years like with all their other "features"!

[Hugh_Pym]

The point about open source...

... is that instead of theorising about 16bit calculations vs fixed point integer representation anybody could look at the code and see. Somebody has obviously done something silly here but we (the users) will never know what and more importantly we will never know why. MS is not the most profitable company in the world by accident; they make all decisions in their (and their shareholders) best interest not ours.

If a new plane overshoots a runway by even half an inch there is an investigation. We don't just accept the manufactures claim that it's just a small thing and will be fixed soon. We lose trust in the product and if it is critical to us we stop using it and find something else. After all what's the unique selling point of Excel? Ease of use? Speed? Value for money? Trustworthiness? or FUD?

[Anonymous Coward]

Just for the record

Excel 2007 can - finally - do more than 65536 rows.

[Dan]

@Excel is also limited to 65536 rows.... conspiracy?

Not any more, if I remember correctly. That was one of the changes for 2007.

[Dam]

THIS IS ALL A LIE AND CONSPIRACY

Look guys, don't be so gullible.

It would have been believable if they had claimed the bug affected Excel 2003.

But Excel 2007?

It doesn't even have a user base :p

[Anonymous Coward]

Number formats...

"Now I wonder why would 2^16-1 show 100,000 ... looks like I'm better off doing my own software and using BigInteger for calculations."

This could be caused by the software switching to a different data format after an under/overflow and not before. The logical fix would be to write software that uses bignums for everything. This way the precision would only be limited by the amount of memory in the system. (bignums are just variable length strings of numbers, usually in the same form as humans write it)

[Anonymous Coward]

More Newsworthy than Halo 3 ?

I just don't get why the BBC haven't reported this in their technology section. Sure it's just a bug, it will be fixed, it's understandable yada yada yada.... Nevertheless this is a company with massive market share and one of their leading products in its market can't do the job it is designed for.

Surely that is more newsworthy than halo three which has been on the top spot for days !?!?!?

[Anonymous Coward]

More Newsworthy than Halo 3 ?

I just don't get why the BBC haven't reported this in their technology section. Sure it's just a bug, it will be fixed, it's understandable yada yada yada.... Nevertheless this is a company with massive market share and one of their leading products in its market can't do the job it is designed for.

Surely that is more newsworthy than halo three which has been on the top spot for days !?!?!?

[Anonymous Coward]

Intel, Microsoft, seen it all before...

Anyone remember Pentium Pros?

http://en.wikipedia.org/wiki/Pentium_FDIV_bug

[Anonymous Coward]

ITs a Lie

Works fine on mine so neeerrrrrrrr

!!Power to Microsoft !!

[Andy Enderby]

Do they need a fresh batch of Code Monkeys at Redmond ?

Hold on, so it doesn't use the FPU, but is doing calculations in FP any way ? Surely this should be worth a couple of words in the user docs..... Oh wait, they don't supply any.

Sounds a bit crappy to me. Almost as funny as the problem with Word 2007 recently documented at the Reg which resulted in spell checker lapsing in to French and staying there..... or the long goodbye in Vista..... or the recent grief with Microsoft Update.....

@Dam thankfully there doesn't seem to be a substantial user base yet, correct, but there will be, sure as death and taxes.

Is it just me or does Redmond QC seem to have left town lately ?

[Hayden Clark]

Pentium bug

An ex-colleague from way back told me that his wife noticed this bug all the time,doing 3-D rendering. As you rotated the object in view, the occasional vertex would leap off to infinity. Ew.

[James Henstridge]

different basis

Perhaps this is a feature, and Excel displays 65535 in base 9.1895587954116795 instead of base 10.

[Anonymous Coward]

Real engineers...

"Fear not. Real engineers would never use Excel for any calculations."

Well sod you then. I'm a real engineer. I use excel all the time.

Mind you, it's that or 'calc'. Boy I love my employers. If microsoft can supply it, they will supply it. There's no two ways about it. Management at it's finest: "It's what I know, it's what I'll force all of you to use". Whether you know a free better tool or not.

And, I'm not bitter.

Excel fixed in X millions days. You're hoping... ;)

[Webster Phreaky]

GROW UP you iLEMMINGS!!!

FFS, why can't you APPL WHORES get it through your thick skulls that there are like infinitely many numbers and it's not meaningful to expect any non-communist, non-tree-hugging profit-making company to support them ALL. Get over it. Excel supports enough of them anyway for most uses and they will add support for even bigger numbers like 65537 (and maybe 65538) in a couple of years once the market matures. So stop WHINING!!!1! The truth is OS X probably doesn't support them either but nobody has noticed because it's not as popular as Windows, why, well maybe because the iPHONEY is overpriced APPLE PIECE OF SH*T!!1!

[Andy]

@Dan

Maybe when they increased from 65,536 rows they did so by expunging the number from Excel's internal 'database of known numbers' (what, you think it calculates? hell no, it looks them up in a book) thereby causing the problem.

[Robert de Ridder]

Real engineers don't use Excel

I wonder why the fact that real engineers don't use Excel (which we can only take your word for) should put our minds at ease. If the problem can exist in Excel there's no reason some sort of problem like that will pop up in another program. In fact with software with a smaller userbase than Excel it is not inconceivable that it takes longer for a bug to be recognised.

[Feargal Reilly]

Holistic Ex-Ching Edition

I seem to have the same version as that other poster - "A Suffusion of Yellow" is all I get as well.

[A J Stiles]

It's happened before

Rounding errors are nothing new. They occur whenever you don't have enough digits to represent a fraction accurately -- and some fractions recur to infinity, so you'll *never* have enough digits. In binary, one-tenth happens to be a recurring fraction: it's 0.0001100110011..... and so on.

When I was at school, some wag discovered that according to four-figure log tables, 2 * 2 = 3.999. Of course, in those days we knew our multiplication tables and would never have dreamed of using logs to multiply two by two.

At least if OpenOffice.org ever did anything like this, the time-to-fix would be measured in *hours* -- and it wouldn't even affect many users anyway, since most OS distributors do a few tests *before* posting packages to their repositories. It would probably only affect people using CVS -- and if you're using CVS, you tend to *expect* bugs anyway.

By the way: the use of 32-bit floating point numbers is a step *backwards*. All the British-built 8-bit micros used to use 40-bit floating point values (8 bits for the exponent and 32 bits for the mantissa). At least we're hopefully all using 64-bit floating point representations nowadays. Alternatively, we could use a hybrid representation with a binary mantissa and *decimal* exponent (instead of 0.11001100....*2**-3, you could write 1.0*10**-1). It would be ugly as sin, but the library would only have to be written once, there's already an API spec to work against, and at least you'd get things like measurements and currency -- which crop up rather often in real world maths, and make heavy use of decimal fractions -- correct to a known, whole number of fractional places.

[Dave]

Excel 2007 Rows

So how many rows can you get in Excel 2007? Would it be 100,000 by any chance?

[Anonymous Coward]

Another nice touch

Don't know if anyone else tried this one but if you multiply the cell containing 100000 by two you get....100000. So put 850 in A1 and 77.1 in B1, multiply in cell C1 (=A1*B1) and then put =C1*2 in cell D2, etc. interesting results indeed.

[John]

Open Office

In open office 77.1*850 = 65535.

Glad I moved over to OO a few years back.

[Anonymous Coward]

Let's speak Swedlish

Gleb: > you can use Mathematica, Matlab och Maple

[Anonymous Coward]

Give them a break already

They obviously needed the number 65536 to get rid of the row/column limitation and took the code from the cell value display stuff so it's not supported there anymore. If they'd left it there as well then you'd blame them for bloated code. Get a life.

[Danny Root]

Maybe a compression problem.

This is just speculation of course, but what it the problem relates to Excel compressing the cell values to 16 bit integers where possible to keep the memory requirements down? This would fit in with the 2007's expansion beyond the 65,535 row limit. In other words it may not be a maths problem but a flaw in the compression code.

[Anonymous Coward]

re:"Nobody yet uses Office 2007"?

I think you really need to look seriously at how you are working.

If you are running into trouble with excel running out of columns then there's something very wrong with how you are handling the data, and if you're trusting 2GB Databases to Acess then you don't understand what Access is for.

I would have to seriously consider whether it's worth doing any business with a company which mis-understands spreadsheets and databases so much.

Ever heard of SQL Server?

[Art]

Title

Forgetting the bloat in Office 2007, this looks like a fundamental fault relating to addessing within the root coding for Excel; as mentioned above 65,536 is 2^16 or 256*256

and when we look at Excel 2003 vs Excel 2007

Excel 2003 Excel 2007

Number of rows 65,536 1,048,576

Number of columns 256 16,384

Number of levels of sorting 3 64

Number of levels of nesting in a formula 7 64

Maximum number of function arguments 30 255

Maximum number of function arguments 30 255

[Mike]

@Maybe a compression problem.

It's almost certainly related to 16 bit issues, maybe the floating point result is 65535.00000000001, but really "why" isn't the point, this isn't some self trained hobbyist code, this is professional, enterprise code from a multi billion dollar organisation and they screwed up.

Perhaps if they spent a little less time hiding flight simulators in the code and a little more time coding and testing the application it wouldn't have happened.

[Anonymous Coward]

@ fixit_f

"I work for an investment bank where lots of stuff among the traders is still done through excel, and while there are lots of unfixed bugs that they still work around they are still as a whole grateful for such a powerful package that can calculate stuff on the fly like excel does."

Its called a DATABASE!

Spreadsheets were created for people who couldn't understand the basic concepts of a database.

And we are talking about real databases, not Access. Access starts having serious problems once it gets larger than 30mb.

[Anonymous Coward]

Cutting through the FUD

It's a bug.

It has certain characteristics and occurs in particular cirumstances.

A fix is urgently needed.

This much is clear.

The sky is not imminently falling down.

Yes, MS screwed up big time and deserve the bad press this brings. Hopefully they will have resolved this a long time before mass take-up of the product.

In the meantime, you need to be able to understand how big a problem this is, how does it manifest itself, when does it cause *real* errors with values rather than just with displayed numbers.

Lots of useful information here:

http://veroblog.wordpress.com/2007/09/26/excel-2007-calculation-bug-displays-apparently-wrong-numbers/

(tiny version of that URL: http://tinyurl.com/3xweb5 )

I'd be interested to know how many people commenting here are using Excel 2007 on a daily basis at all, and how many of those are "power users".

[James Pickett]

Verified

At least it sounds like MS is attempting to verify their code. IIRC, Steve Ballmer was asked about this a few years ago and (like Gandhi when asked what he thought of Western civilisation) he replied that it would be a good idea.

AFAIK, Excel's arithmetic has never been officially verified - everyone just seemed to assume that it had been properly programmed...

[Anonymous Coward]

Quick check on an empty Excel sheet

850 shares of BNP Paribas @ €77.10 = €100,000

[Anonymous Coward]

Hmm

Does this explain Northern Rock's recent problems?

"We're only worth 65.535 billion? My spreadsheet says 100 billion!"

[Ian Ferguson]

Missing the real question

I think all this discussion of mere calculations is missing the point of Excel. The real question is, how do we launch the next-generation flight sim easter egg from within Excel 2007?

[Ian Watkinson]

No service pack

No upgrade.

Who's stupid enough to run Microsoft V1 anything for serious work?

No vista until sp1, no office 2007 until sp1.

Mind you, no vista at all is looking more attractive.

[Anonymous Coward]

I like Webster's comment best.

This bug is so hilarious that only the trolls can possibly have something relevant to say.

I note though, as a troll, that Intel spent many years convincing us that their 32-bit processors were superior to competing 16-bit processors.

Apparently they didn't think we believed them. So now they're demonstrating what can go wrong with those old outdated 16-bit jobbies.

[Anonymous Coward]

Sinclair Spectrum BASIC

This sounds similar to the ZX Spectrum bug whereby "PRINT -65536" would display "-1". (You can draw your own conclusions.)

It was a result of the number type automatically switching from floats to ints when possible (so that your "for x = 1 to 10" loop wouldn't suffer from floating point rounding errors).

-65536 was ambiguous in this format (converting the int resulted in the the same bytes as fp -1.0 or something like that).

[Anonymous Coward]

Spread sheet error rate study

This data is scary.

http://www.louisepryor.com/showTopic.do?code=errorRates

[Carrie]

Re: This is bad Re: Big trouble

As a nuclear engineer:

No, we don't use Excel except to check hand-calcs. Anything that can't be done by hand is fed into Maple or MatLab, and independently verified by a 3rd party.

As a former engineering student:

The first thing we were taught, and taught, and taught again and again - don't trust the software unless you know the logic. Half of my classes were learning the logic behind the programs. In Canada, engs take a vow - and that vow isn't worth spit if you're relying on unknown assumptions. I can only assume the same standard applies.

[Gleb]

Oh, btw

format a cell as "date", type "60" and apparently you'll get 29/2 1900, a day which didn't even exist! A professor said this to me today, so I don't know is it's true or not.

[Anonymous Coward]

@Gleb

That's day 60 of 1900 (the base year for Excel). According to Microsoft February 1900 was a leap year... this is enshrined in their OOXML standard which shows just what a pile of shite it is.

[Andy]

Boundary condition

I once had to do some maintenance of really crusty code in an Estate Agency package. I stumbled upon one fragment where the code did something completely different if a street address contained a particular number. None of my colleagues would own to it, and equally, none would sanction me taking it out for fear of breaking something. It was obviously a debugging hack put in back in the mists of time and never taken out again.

To me this Excel behaviour smacks of someone debugging a boundary condition, then forgetting to remove the test code.

Doesn't say much for Microsoft's internal development tools (or processes), though.

-A.

[Michael]

@baka

"My Analyst wife reminded me that Excel also has a maximum of 65536 rows...."

Your analyst wife is only half-right.

Excel 2007, which is the version experiencing the bug, can store 1,048,576 rows.

[Dave Morfee]

Is it such a big deal?

If this only happens with the 65,535 how often in daily calcualtions is that figure actually arrived at?

Ok it was a cock up on the display of the number, but the sum does come to the right number though

Dave

[Danny Roberts]

Common Sense?

******850 shares of BNP Paribas @ €77.10 = €100,000********

Not having a go at the poster above, but does demonstrate my point: I was always taught to at least have an 'idea' of what the result should be. The amount of times I have seen people tap numbers into a calculator, mistype one and believe the result implicitly! "The calculator says so, so it must be true!".

**********The first thing we were taught, and taught, and taught again and again - don't trust the software unless you know the logic.**********

Do you code all your software packages yourself, or only use Open Source and then go through all the code yourself? At some point, you have to believe what a software package tells you, but you also have to use your common sense.

[JakeyC]

@@Gleb - 29/02/1900 is a 'feature'

From Chip Pearson (http://www.cpearson.com/Excel/datetime.htm):

In Excel, the day after 1900-Feb-28 is 1900-Feb-29. In reality, the day after 1900-Feb-28 was 1900-Mar-1 . This is not a "bug". Indeed, it is by design. Excel works this way because it was truly a bug in Lotus 123. When Excel was introduced, 123 has nearly the entire market for spreadsheet software. Microsoft decided to continue Lotus' bug, in order to fully compatible. Users who switched from 123 to Excel would not have to make any changes to their data. As long as all your dates later than 1900-Mar-1, this should be of no concern.

[hans-peter carpenter]

HAHAHA

My wife is a "number cruncher" and has been using excel 2007 since March, at the time I told her that her IT guy was nuts - she confirmed the undocumented feature. She is the only one in the office with that version, it had/will not be/en tested - she was new, got a new computer with "un-tested" new software ....

[david]

Lotus 123 'bug' and XLOPERs

But it is unlikely that the date calculation was a 'bug' in Lotus 123. It simplified the arithmetic and data storage requirements, so it was a reasonable trade off for the hardware at the time. It's not a reasonable trade off for the hardware available now.

But the (Lotus 123 compatible) spreadsheet is the reference calculation for most of business right now, and the painful compatibility mode in Open Office shows just how difficult it is to get around that problem.

If the Open Office example solution to the compatibility problem wasn't so fragile and error-prone, I'd have a lot less sympathy for the Microsoft position on standard spreadsheet formats.

By the way, values in MS spreadsheets are stored as XLOPERS, which became variants in VB. XLOPERS are structures which may have integer/float/string etc values. The calculation engine is a highly optimised hand-tuned piece of code which recalculates only affected cells, not every cell on the sheet, so it does a graph analysis as well.

Simple math libraries like the standard c library typically just use simple rounding to get the display format for floating point numbers. A slightly more complex algorithm gives you rounding to the shortest posible correct decimal. So cPython shows 65534.999999999993, and Excel is supposed to show 65535, but it's the underlying binary value that is used for calculations

Excel only rounds to the display format for display. What we have here is an error in the Mathematics used to calculate the display value. We have examples of three binary floating point numbers where the display value calculation is wrong, and examples of how to generate them. Has anyone published the 'other 3', or was the theory that there were 'just 6' a bit of wishful thinking?

[J]

Re: Re: Software vs Hardware

"apart from the aggressive and slightly substandard intelligence / illiterate manner in which you raised it"

Oh well, I can't be bothered to put a disclaimer at the bottom of everything I write stating that I am not a native speaker of English, and have been speaking it for 5 years now. And no, I'm not 5 years old, before someone inevitably makes the suggestion. :-)

And it's not like your English is exactly stellar there either, I'm afraid. Maybe you have the same excuse as I do?

Now, why are you afraid of the aggressiveness? Chill, nobody is after you...