Excel Fortune Cookie (…in bed?)

Here’s a fun little fortune cookie program. The fortunes are generated from a list of random words. I developed four different grammars for the fortunes, which you can find in the backend data. (Is “grammars” the right word? I’m not sure.) Some of the fortunes, like the one above, seem to make sense. Here are a few I’ve captured, which appear thoughtful and humorous, perhaps even poetic.

The ugliest way finds evil.
If you satisfy this empty girl generously, you are the one who … does it even matter?.
Who wants the answer to be 42? The mammoth formula does!
Who finds true love? The beautiful water does!
The easy government gives up now.
Want the story now? Reluctantly face your family.

Most fortunes, however, come out as nonsense. For example:

Want the head now? Frenetically melt the day.

That’s great advice.

Feeling lucky? You should.

Fortune Cookie.xlsx (If the file doesn’t download immediately from Google drive, press the black down arrow in the upper left of the screen, under the menu.)

Informal Poll: Your screen resolution of choice?

Most of my work is developed for wide screens monitors. How about you? I usually develop my work for 1366 x 768 resolution, but sometimes I’ll have to develop for different resolutions to accommodate my clients. Sometimes, I’ll include a fit-to-screen button that works will about 80 percent of the time.

So let’s do an informal poll. In the comments section, post your screen resolution of choice – and any tips you have for accommodating resolutions different from yours.

Also, have a great weekend!

The Excel Lab: Soundex Search

Every once in a while, I have a spreadsheet that sits in my ‘lab’ for way too long. At a certain point, I have to admit that I’m never going to finish it completely or do a full blog post on it. I’ve decided just to release one such spreadsheet as is. Perhaps you will be the one to carry it forward? (or, write to tell me I’ve wasted my time?)

Soundex Search

I while ago, I read about a phonetic algorithm called Soundex. Soundex is useful (well, there’s some argument about that) for searching through a list of surnames while compensating for common misspellings in those names. Using a dummy dataset and UDF developed by Richard Yanco, I created the following:

As the animation demonstrates, you can type in your search term into the ActiveX textbox and both the listbox and graph will automatically update to show you relevant search results and corresponding values. I’ve added some additional search functionality beyond Soundex as well. For each name in the dataset, I’ve assigned a random value to plot.

I’m rather unconvinced of the usefulness of this. I’ll put it you to see if you have any luck with it. For what it’s worth, the mechanism is formula driven. I didn’t use VBA except to set the original Soundex values for each surname (which, you could also probably do without VBA if you were so inclined.)

Download the source file:

soundex.xlsm

Two Great Applications of Rollovers

I want to highlight two applications of the rollover technique that are really quite phenomenal.

Square Charts and Treemaps in Excel

If you haven’t seen the work of the Frankens Team yet – you’re missing out. They’re a group of…geniuses, really…who investigate and push Excel’s limit. Very recently, they wrote on the development of square charts and treemaps in Excel combined with the rollover method as shown below.

Read more about it, here. And be sure to look through the rest of their catalogue.

Excel Boilerplates

Mark Kubiszyn of Excel Boilerplate and Kubiszyn.co.uk used the technique in the development of two very slick boilerplate spreadsheets. His works adds a much needed streamlined process to creating and inserting new hyperlinks – as well, he’s expanded the ways in which you can link from hyperlinks. Please do take a look at his work, here. Also take a look at his YouTube videos on the subject.