Friday, October 9, 2009

Still committed... or maybe I should be committed.

I came to the conclusion a few months back that teaching statistics should go hand in hand with teaching the ins and outs of the Excel spreadsheet formulas that are useful in statistics. If a student in a beginning stats class ever has to do some statistical analysis in some future gig, it's way more likely they will use Excel than any standalone stats package like SPSS or Minitab. And even if a student never uses statistics again, learning how to write a formula and make a chart are useful skills in Excel, and knowing Excel is a useful skill in life.

Win-win situation.

So I'm diving head first into Excel, having a lot of fun, when I start noticing glitches. Some are minor and have workaround options, but some don't, they are honest to goodness bugs.

Here's a minor problem. The binomial coefficients, the numbers in Pascal's Triangle, the stuff I wrote a whole damn website about. If I type =combin(10,5) into Excel, it correctly says 252. This means that if I want to choose five things out of a set of 10, there are 252 different combinations. If I had a Texas Instruments calculator, I could find the same option under the Probability menu, usually named nCr, either 10 nCr 5 or nCr(10,5), depending on what model you have.

What if I type =combin(5,10) instead, possibly by mistake? Now, I'm asking how many ways there are to choose 10 things out of a set of 5, which is impossible. Excel will tell you #NUM!, which is it's way of saying "Warning! Warning! Danger, Will Robinson!" A TI calculator will give you the number 0.

The calculator is correct. The proper way to define a binomial coefficient where the second number is bigger than the first is 0. It's not illegal, it's just zero.

A strong pattern is emerging. If you get a different answer in Excel than you get on a TI calculator, the calculator is correct and Excel is wrong. This has happened more than once. There may be a counter-example, but I haven't found it yet.

I found a much weirder bug this morning, one that wouldn't have a workaround, but I haven't been able to repeat it yet. Ah, the joys of software testing.


Distributorcap said...

well excel does return the correct answer when you divide by zero.

i assume it gives you something similar if you try to take the square root of a negative number

Matty Boy said...

True about divide by zero. Square root of a negative number is a different case. The TI-89 gives the semi-correct answer of "error: non-real result", which is inconsistent since they actually have a button with i on it.