The Obscure Function Challenge

Challenge for April 14, 2006: The Obscure Function Challenge

UPDATE 4/17/06: Dwayne Kuemper is the winner of this challenge. See below.

I am busy this month writing a new book for Excel 2007.

In the past, most large Excel books give a generic example of Excel's 350 functions, often copied from Excel Help. Instead, I would like to come up with a real-world example of something useful that you can do with every Excel function. I am plugging along with this task, but I have hit a brick wall with the FACTDOUBLE function!

=FACT(9) calculates a Factorial: =1*2*3*4*5*6*7*8*9. This is useful. It is great for figuring out permuations.

But Excel has another function called the Double Factorial.

For even numbers, =FACTDOUBLE(8) is 8*6*4*2.

For odd numbers, =FACTDOUBLE(9) is 9*7*5*3*1.

Simple enough. I understand WHAT it does. But WHY would anyone use it?

In nearly a million posts at the MrExcel Message board, FactDouble appeared twice.

I asked my friends at Microsoft to check through the SQM data to see if anyone has ever used the function, but SQM data doesn't capture this. They did point out that I am not the first author on this quest. Norman Harker asked the same question back in 2001.

My old hometown math wiz knew the function, and could quote from the MathWorld.com article about how interesting the function is for n<0, but MathWorld does not offer anything about why you would use it. It is interesting that the Mathworld chart is only interesting for instances of n<0, and the Excel FACTDOUBLE won't calculate for n<0. The math wiz friend did speculate that there must be some actuarial scenario where you would want to take out every odd number but could not offer an example. (I'll admit, if I received a call out of the blue, from someone who I haven't seen for 25 years, asking about some strange function, I might make up an answer like this just to get them off the phone...).

There was an Internet game going around recently where people were trying to express every integer from 1 to 100 using only the number 4. First - there is this article showing how to do it for numbers up through 12, and then this thread proposes using a double factorial to solve the problem for 51. OK - so I get it - there is a game to solve this weird problem, but the project manager at SuperCalc or Lotus or Quattro or Multiplan certainly didn't add FactDouble to a spreadsheet program just to solve this crazy problem.

The challenge is simple. Send me an example where you use FACTDOUBLE for some useful real world problem. For bonus points, give me a simple explanation of why FactDouble is useful. The prize: listing in the acknowledgements of the book and an autographed copy of Special Edition Using Excel 2007. Send your entry to consult @ mrexcel.com. The contest will run until someone comes up with an answer, or until the book goes to print.

Results

Dwayne Kuemper from Canada sent in a perfect link. It turns out that FACTDOUBLE can be used to calculate certain probabilities in the Texas Hold'Em card game. Since Texas Hold'em now occupies entire cable channels in the United States, I absolutely deem this a "real world" example. It was brilliant of Microsoft to add it to Excel a decade ago. They were clearly ahead of their time, probably predicting the rise in popularity of Texas Hold'em. To read all of the details, see this link.