April 24, 2010

how to make boggle: geeking out in excel

i talk a big game when it comes to boggle (the parker brothers word search game).  it's pretty much my only talent.  consequently, my coworker had asked me bring my travel boggle on our business trip last week so we could play.  so, there we were: thursday night, sitting on our connecting flight back to LA and he suggests we play boggle.  of course, i'd left it at home.  for the first 10 minutes of the flight, we whined, lamenting that we couldn't play.  that is, until i had a brilliant idea: i could MAKE a boggle.

the timing was right because i'd just hosted a company-wide microsoft excel training earlier that day.  it was the perfect opportunity to showcase my nerdy excel model-building skills.  for those of you who think this is super simple, i want to remind you that you can't just assign a probability to each letter, because once a letter is picked on a die, the other 5 letters on the die are out of the running.  you need to write a model that randomly picks a letter on each die, and then randomly assigns a position to the 16 selected letters on the board.  i buckled down, employing VLOOKUP, HLOOKUP, RANK, RAND, and a macro.

twenty minutes later, we had a fully functional game of boggle, complete with a clickable button to "shake" the box of letters for a new game.  i set my screensaver to turn off the monitor after three minutes as a de facto timer.  we played for the rest of the flight, and it was awesome.  i had figured out how to make a boggle game using nothing but excel.

the only thing that wasn't 100% accurate was the distribution of letters (which i just had to guess, at the time).  i've subsequently adjusted the model to reflect the letter distribution in a real boggle game, so my excel model is exactly like playing the real game.  so nerdy.  so sweet.

if nothing else, five years of management consulting was good for something.


Meanwhile, back at the ranch said...

nerd genes at work. you make me proud.

Dustin said...

I was promised a how-to!

George Mousavi said...

Useful article.I like it very much.
Excel training NY