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)

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

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

Intel, Microsoft, seen it all before...

Anyone remember Pentium Pros?

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

ITs a Lie

Works fine on mine so neeerrrrrrrr

!!Power to Microsoft !!

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 ?

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.

different basis

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

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

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!

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

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.

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.

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.

Excel 2007 Rows

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

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.

Open Office

In open office 77.1*850 = 65535.

Glad I moved over to OO a few years back.

Let's speak Swedlish

Gleb: > you can use Mathematica, Matlab och Maple

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.

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.

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?

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

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

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

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

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

Quick check on an empty Excel sheet

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

Hmm

Does this explain Northern Rock's recent problems?

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

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?

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.

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.

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

Spread sheet error rate study

This data is scary.

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

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.

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.

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

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.

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

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

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.

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

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

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?

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