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

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.

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.

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.

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 :(

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.

OOXML version...

<ComputeLikeExcel2007>

77.1 x 850

</ComputeLikeExcel2007>

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

I keep getting

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

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

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.

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.

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.

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.

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

:(

I still can't...

... see the Paris Hilton link!

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.

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.

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

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.

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

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?

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 ?

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.

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.

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

Hat, Coat, and Taxi for Microsoft

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

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

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.

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.

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.

Re: This is bad

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

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?

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

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

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

@Fix due soon @we need to know

RATFLMFAO

Aussie Doc

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

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

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

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.

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.

@Andy

Andy Barber wrote:

"...see the Paris Hilton link!"

Its clear. She can't count either.

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"!

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?

Just for the record

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

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

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

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