f13.net

Just in case some of you are using Excel 2007: Bug in Excel 2007 (http://groups.google.com/group/microsoft.public.excel/browse_thread/thread/2bcad1a1a4861879/2f8806d5400dfe22?hl=en#2f8806d5400dfe22)

Basically some calculations that should come out to 65535 are showing as 100000.

Edit: fix:

http://blogs.msdn.com/excel/archive/2007/10/09/calculation-issue-update-fix-available.aspx

Jesus. I just tried that on three earlier versions of Excel I have and it calculates fine. How do you mess something like that up?! It's almost like they had to try...

Because of the binary representation.  2^16=65536, and 2^20 is 100,000 in hex.  2^16 is also two bytes.  Somewhere something broke, had an overflow, whatever.

Computer Science 101 ftw.

Overflow errors are so freakin' common. We try to right overflow-proof classes as much as we can here, but you always miss something.

I'd like to take this opportunity to express the fact that Office 2007 is a badly designed crock of shit.

I thought about this and can't really see how this would break any of my spreadsheets. 

Cell referencing? Errr, 65k rows and you should be working in a real database.

External database calls?  See above.

Dates? Maybe.

Code? Maybe, but what the hell would you use integers of 65k or higher for?  An array that big would break excel.

Finance calculations?  We work to four decimal places in unit prices - the likelihood of hitting 65535.00 is basically nil.

Now if it had been floating point calc I'd be more worried...

I know what I should ask my salary to be.

We usually use MiniTab but test data and process evaluation is handled pretty well with a spreadsheet.  Linear regressions (amongst many other tools) and such on a few million data points is pretty handy, especially when you can just use it and not have to have all kinds of project specific programs to support.

As I recall Excel 2007 was supposed to lift the limit on number of rows you could work with (MiniTab is limited to 10 million) though I don't think anyone here was seriously considering using it.

When you are doing calculations on millions of items there is a pretty good chance that one of them might end up being 65535.

buffer overflow... someone didn't increment their array properly    :lol:

in other news, "Better Uninstall: AOL Instant Messenger Has Giant Worm Hole" (http://www.marketingvox.com/archives/2007/09/28/better-uninstall-aol-instant-messenger-has-giant-worm-hole/).  Good times.   :nda:

I'm not sure why 850 units times 77.10 would be such an impossible thing to encounter.

In the spreadsheets I use the calc would have to be 850.0000 times 77.1000 since all the inputs are to four decimal places (no rounding) - that's one in a hundred million right there.  That's just the calc.  There is also error checking at an aggregate level so the total of all the units is added up and multiplied by the price of those units to get a check on the total of the individual transactions.

Um ..It is broke. Trust it only if you want to be fired.

Fixes:

http://blogs.msdn.com/excel/archive/2007/10/09/calculation-issue-update-fix-available.aspx