Fri 29 Sep 2006
I need help from anyone out there who works on both a Mac and a Wintel box on a regular basis. Either I’ve got a particularly defective copy of Microsuck Office 2004 for Mac, or I’ve discovered a gaping pus-oozing hole of retardedness in Excel for Mac. Either way, those security-sucking monopolistic pricks are really making my already sleep-deprived life a real pisser, and I don’t think I’ve ever wanted to shoot someone in the testicles more in my entire life.
I’d like you to perform the following experiment in Excel 2004 on a Mac to see if your copy is brok’d, too. In a new spreadsheet, in range A1:A5 enter, simply, 1, 2, 3, 4, 5 — your X values. In column B, enter the corresponding Y values 6, 11, 18, 27, 38. This data set represents the quadratic equation y = x2 + 2x + 3. If you want to enhance the fun, select that data, create a scatter plot of it, and add a trendline: polynomial, power 2, and tick the option to display the equation and you’ll see what I just told you.
Now, if you have to perform polynomial regressions on a metric ass-ton of data sets like I do, you don’t want to have to fit a trendline to a plot of every single one of them and write that crap down. Fortunately Theoretically, Microsuck Excel gives you an alternative approach with the LINEST function. You can write an array formula using LINEST to output a range of cells that are the coefficients of the equation you’re trying to fit to your data. Here’s how you would (theoretically) determine the three coefficients for the quadratic equation that corresponds to the data you’ve typed into A1:B5.
Select cells C1:C3* C1:E1. Then type the formula
=LINEST(B1:B5,A1:A5^{1,2},TRUE,FALSE)
and finish it off by hitting Cmd-Enter instead of just regular Enter (this is how you establish an array formula that outputs into your entire range selection).
That’s it. In theory, you should get the coefficients of your quadratic, namely 1, 2, and 3, in cells C1, C2, and C3* C1, D1, and E1. I, on the other hand, get -3.0625, 13.3125, and 4.1875, because Bill Gates is a dweeb. Please let me know what the hell you get. If you arrive at anything other than 1, 2, 3, entertain yourself further by repeating the exact same algorithm on Excel for Windows (except that on Windows, you finish off an array formula with Shift-Ctrl-Enter). If you get 1, 2, and 3 on a Mac, let me know; I’ll try re-installing, or take my Office CD back to the store and shove it up someone’s floppy drive. And if you think I’ve just got my syntax screwed up, look at the tutorials here and here and then bite me.
And if you try to blame my platform for this glitch, I just might chew your nose right off your face.
So. Expletive. Angry. Must. Kill.
This is why space probes crash into Mars, people!
* Update: Excel book author John Walkenbach pointed out an error in my blog entry, but not in my spreadsheet. It is in fact a horizontal range C1:E1 that has the output of LINEST. I mis-typed the range here at 4:00 in the morning trying to prepare for my research review. Excel is still brok’d though. See screenshots below.
September 29th, 2006 at 10:29 am
Ugh. Yes, I cannot use Excel on the mac and trust it. I have no idea what the problem is for those geniuses to not be able to fix this.
October 2nd, 2006 at 4:49 pm
I’ve never used Excel for Mac, but I think you may need to select a horizontal range for the LINEST array formula (that’s how it’s done in XL/Win). Instead of C1:C3, try C1:E1. Or, if you need the values in a vertical range, use:
=TRANSPOSE(LINEST(y,x^{1,2},TRUE,FALSE))
October 2nd, 2006 at 5:59 pm
John,
Wow, thanks for coming by! None other than the Excel guru himself, ladies and gentlemen. I\’ve got one of your VB for Excel books around here somewhere.
As I\’ve edited the post to reflect, I did in fact select C1:E1, I was just too zonked at 4am to type clearly. And according to the Excel Help on the Mac, it\’s the same syntax as Windows. It just seems to be on acid. I\’ve added screenshots of the Mac and Windows spreadsheets to this post, just for posterity\’s sake.
And here\’s another interesting thing to chew on: I was just messing around with the format of inputting an array equation on the Mac version just to see if that\’s different (i.e.,
LINEST(B1:B5,A1:A5^2,TRUE,FALSE)and the like — which didn\’t work), and in one case I flipped the order of the exponents toA1:A5^{2,1}. Now, in Windows, this has the somewhat predictable effect of reordering the output in C1:E1 to be {2,1,3} instead of {1,2,3}. In Mac? It\’s now {-4.959375, 11.590625, 14.315625} instead of {-3.0625, 13.3125, 4.1875}. Dubyah. Tee. Eff?Another experiment with syntax that also failed:
A1:A5^{0,1,2},FALSE,FALSE. That produces {4.8, -13, 26.8}. Oh well.One final note: I repeated this glitch on my iMac G5 just to make sure it wasn\’t some dingleberry due to porting to the new Intel chip system. I wonder where my Office:X CD is, I could see if it\’s specific to Office 2004.
I\’m thrilled to have you pop in, John, and would be most grateful for any light you can shed on this — or any upward feedback to people who can fix this. Surely they\’d listen to you more than the futile feedback I left at Microsoft.com. I can use Excel on this Wintel box at the office if I have to, but I\’ll finish my PhD much, much faster if I can actually, you know, do math on my laptop with the software I paid good money for.
October 2nd, 2006 at 6:08 pm
One more thing: If I take a spreadsheet from Windows in which LINEST worked correctly and open it on a Mac, it looks fine. But when I select the output range for LINEST, hit ctrl-U to edit (or just click in the formula edit window), change nothing, and hit Cmd-Enter, the correct numbers are replaced with the glitchy garbage.
If you do have time to mess around with this (I understand you’re busy, but hey, this could make an interesting book update!) and want to send me any formulae or spreadsheets to try out on the Macs, my email address is just me@ this domain name.
From one WWR fan to another, thanks.
October 2nd, 2006 at 7:15 pm
I’ve heard far too many stories like this about Excel/Mac. That’s why I just pretty much ignore its existence. I don’t even try to make my apps compatible.
You can always try searching (or posting a question to):
http://groups.google.com/group/microsoft.public.mac.office.excel
Good luck.
October 3rd, 2006 at 12:50 am
Ah, thanks for the lead. I should have thought of that; I always forget to check Google Groups (you’d think they’d make such results more obvious when searching for things like they do with images and addresses). Sure enough, this is a recognized bug — “Hi, thanks for spending half your paycheck on our software, oh and by the way, go screw yourself, it doesn’t work and we don’t care.” — but there’s an easy enough workaround here: use the TRANSPOSE function on both input arrays, and use a semicolon instead of a comma for the exponent array. Extreme lameness. I love corporate accountability.