Yesterday I posted a request for a method to perform a probabilistic lookup. Lots of good ideas. I chose the method suggested by Mpemba, mainly because he demonstrated that it works.

My goal was to create a workbook that generates a list of random fake names. I got it down to a single formula that uses two parameters: The probability of a male name, and the probability of having a middle initial. I think there’s about 2.7 billion unique names than can be generated. This sort of thing has lots of uses: generating data for samples files, and… uh… Well, I’m sure it’s good for something else. Anyone in the identity theft industry?

Here are some random names that it spit out: Delores A. Spear, Jason Dyer, David I. Hearn, Paulette Crissman, Gerald Olson, Kathleen Oleary, Joan Smith, Curtis Watt, Latina Murguia, Boyd Therrien, Rita Z. Morales, David Rapp, Ma Anderson, Kurt Cunningham, Nancy Hackney, Stacey I. Harris, Heath Hunter, Trevor Sisson, Carol Papenfuss, Patrick Lenhardt, Ian Grayson, Earl T. Wiggins, John Brooks, Billy Barkley, Antionette Adkisson, Edward I. Gibson, Lisa Stillman, Amanda Arthur, Lillian L. Archuleta, Virginia Ryan, Barbara Fritts, Robert Jones, Jacqueline Lowe, Donald Steiner, and Joyce Brown.

These names certainly sound real to me. In fact, I think I went to elementary school with Barbara Fritts.

In any case, feel free to download it and try it out: namegenerator.zip. The ZIP file is 790K, and the XLS file is about 3.3Mb.

Here’s a challenge for someone. Well, maybe it’s easy, but it’s challenging for me. For lack of a better name, I call it probabilistic lookup. Say you have a list of names, ordered by popularity (according to the U.S. Census Bureau):

Mary
Nancy
Pamela
Beatrice
Deborah
Enid
Lolita
Delta
Mayola
Concha

How can you randomly select a name such that names at the top of the list are more likely to be selected than names at the bottom of the list? In other words, if you randomly select 1,000 names from this list and then do a sorted frequency distribution of the selected names, they should (roughly) appear in the same order as the original list.

It needs to be generalizable to handle a much larger list (~10,000) . I’m not seeking statistical perfection, just a reasonably accurate way to give names higher on the list a greater chance of being selected, and names lower on the list a lower chance of being selected.

I’d prefer a formula solution, if possible.

Did you ever stop to think of what goes on behind the scenes in Excel when you enter something into a cell? I came up with a rough list:

First keystroke:

• If the cell locked and the sheet is protected, display error
• Update the formula bar and/or the cell
• If it’s an equal sign, at symbol, minus sign, or plus sign change status bar and Name box
• Disable inappropriate menu items
• Suggest Autocomplete, if possible and enabled

Other keystrokes:

• Update the formula bar and the cell display
• Monitor keyboard for things such as Ctrl+B and Ctrl+I
• Monitor keyboard for things such Shift+F3 to insert a function
• Monitor toolbars for formatting changes
• Suggest Autocomplete, if possible and enabled

Enter key (or arrow key):

• Check for valid formula and display error or pop-up
• Check for names used
• Check for external references, and retrieve values if necessary
• Check for dependent formulas and recalc if necessary
• Check the 1-2-3 formula evaluation setting
• Check for data validation
• Check of conditional formatting
• Check the AutoCorrect list and adjust if necessary
• Check if the cell has a style, and adjust appropriately
• Check to see if it’s part of an array
• If it’s a number, display it using appropriate number format
• Display it using correct alignment
• Display it using correct font, size and attributes
• Adjust row height, if necessary
• Autoadjust column width if appropriate
• Update formula bar
• Check for a VBA event-procedure (e.g., Worksheet_Change)
• Check if it’s a hyperlink
• Activate next cell if appropriate
• Display formula error SmartTag if necessary
• Check to see if the cell is in the Watch list
• Check to see if cell is contained within a camera tool picture
• Update charts, if necessary
• If text-to-speech is active, speak the cell contents

What have I missed?

I’ve been using the Excel 2007 beta for quite a while. Customizing the new ribbon UI has always been a mystery. Until today. If you’re interested in learning how to customize Excel 2007’s user interface, follow these steps:

1. Read Savraj Dhanjal’s brief article: Ribbon Extensibility: A VBA Sample, and then download the RxDemo.xlsm file.
2. Go to OpenXML Developer, and download the Custom UI Editor Tool.
3. Open the RxDemo.xlsm file using the Editor Tool.
4. Study it, and then experiment on your own.

I’m encouraged. Creating a new tab to house my PUP v7 tools should be a piece of cake. Well, not quite. It’s probably about 10 times more complicated than using the old CommandBar object model, but at least it’s do-able.

Most people probably know about this, but I just discovered it today. You can use Excel’s Speak method in a function. Simple example:

Then you can write a formula that vocalizes the contents of a cell whenever the sheet is calculated. Like:

Or, add a condition using and IF Function. In this case, you hear the lovely voice only if the sum of the values in column A exceed 25,000:

It’s possible that there are some very good uses for this. But I can’t think of any. But it’s kind of fun to play around with.

By the way, Excel’s text-to-speech feature first appeared in Excel 2002, so this won’t work with earlier versions.

What do you think of this? It’s a free product called Invisible Basic (and no, I didn’t link to it just because he mentions my name).

Invisible Basic is an Excel Add-in that replaces the VBA code within an Excel Workbook with obfuscated (very hard to understand) but functionally equivalent code. Simply “Save Invisibly” to share your Excel solutions…without sharing their source code.

Isn’t Excel’s password protection enough? As award-winning Excel author John Walkenbach has observed, the existence of web-based Excel “password recovery” services implies that the answer is NO. With only the unreadable (but functionally equivalent) code produced by Invisible Basic to go by, your competition will need a lot more than a “small service fee” to figure out what you are up to.

I haven’t tried it, because I have no use for it. One of the best business decisions I ever made was to sell the source code to my Power Utility Pak add-in at a very reasonable price. It’s very likely that some bits of my code have been incorporated into other commercial products, but that’s not a major issue for me. I don’t think I’m capable of writing VBA code that’s so good that any intermediate VBA coder couldn’t duplicate it.

Is the theft of your Excel VBA code really a problem for anyone?

Some basic access stats for Daily Dose of Excel are available here. The chart below shows the percentage of visitors by days of the week.

Clearly, most people access this blog from work. Many people take Fridays off, so that would explain the lower number for Friday.

But why are there fewer visitors on Mondays? Are Mondays generally busy days? Too busy to waste time with blogs?

And why are there more visitors on Sunday, compared to Saturday?

Here’s a list of publicly-reported spreadsheet mistakes, compiled by the European Spreadsheet Risk Interest Group.

These stories illustrate common problems that occur with the uncontrolled use of spreadsheets. We say how we think the problem might have been avoided.

An obvious form of risk avoidance is simply to check your work before sending it out. For important spreadsheets, a second pair of eyes (‘peer review’) is even better. Where stakes are high, a thorough test and audit is a further defence.

How about some personal anecdotes. Have you ever been embarrassed by a serious spreadsheet error?