evannichols (evannichols) wrote,

  • Mood:

Smurf. Here's This, Instead...

My life is being temporarily consumed by preparations for Stumptown Comics Fest, which I will indeed write about, as well as some thoughts on the recent movies I've been watching via Netflix (and I can go on about how I'm really enjoying Netflix), but I haven't written all that up yet, and I have this post that I wrote a couple weeks back, so let's go with it for now. It's long. I'm just saying.

What follows is an IT-Geekery post that serves two purposes:
1. To give a non-proprietary view of the kind of thing I do for work.
2. To show off some Excel spreadsheet geekery.

If neither of these things appeal to you, don't click through. If you're at all curious (and you really SHOULD be), l waiting for you behind this cut!

You've probably never thought to yourself, "I wonder what sort of things does Evan do for his Day Job?" There's not an easy answer, because it ranges from simple programming tasks to large-scale Business Process Improvement. This example is not a literal one (no client has ever asked me to make Bingo cards), but more a demonstration of the problem-solving that is frequently a component of my efforts, and some commentary on the process.

I decided that I wanted to create Bingo cards with a bunch of things that I do on a weekend, so my family, friends and fans can play Evan Weekend-Activity Bingo. While I could do them by hand, why do that when I can create an Automated Process? Automated Processes are Always Better™*. To build one, we go through a Definition – Development – Validation process.

First, we define what it is we want. In Real Life, the Client usually says something like: "I want a report of the Customers that I need to call during the week." While they'd be happy just leaving me alone at that point to magically create this report, I actually need to find out exactly what they mean by that. So I'll ask how they know they need to call a Customer. They'll say they'll call if the Customer has a backordered item or if delivery is behind schedule. Pro Tip: While these two points a appear to give a simple, workable definition, the first response is NEVER the full requirement.

Further questioning reveals that the Client actually has a bunch of reasons that will trigger a call: If other order problems exist; or if the order quantities are significantly above or below prior orders; or if the Client just hasn't talked to the Customer for a while, etc. It's not that the Client is intentionally withholding information, they just haven't consciously enumerated all the triggers before. So part of my job is coax that information out after the Client believes they've already told me everything I need to know. This is one of the steps where a good Business Analyst really earns his kibble.

Depending on the complexity of the request, we could end up with a long list of specifics. For my example, it's fairly short.

Desired Results/Requirements:
1. Create Bingo cards with items randomly selected from a list of 75 items.
2. Each card has 25 items, in a 5x5 grid.
3. Of those items, 24 are randomly selected.
4. Center square is FREE (and described as "Watching Funny Cat Videos").
5. Any given item can appear in any row or column (except the center square).
6. Items cannot appear more than once on a card.

List of items.
Random number generator.

Bingo card.

Most Development Methodologies have a sign-off point at the end of the Definition Phase, where the Client is essentially saying "If you give me a product that meets these defined requirements, I'll be satisfied." Which is never really true; they always change their mind. Always.

So now I sit down to solve the problem of how to create the card so that it meets requirements. I decide to use Excel, because it can do all of the things needed, and I have Excel. It's almost always better to go with existing technology.

First thing, I turn calculation to 'Manual'. We're going to be reviewing results of random-number generation, and if updates are left to "Automatic," then Excel will recalculate every time something changes. That will annoy us greatly. This way, everything stays the same until we hit 'F9'.

It's pretty easy to mock-up a Bingo card that inserts items in the squares with a function that selects an item using the random-number generator. In Excel, it could look like this: =INDEX(List!$C$2:$C$76,INT(RAND()*75+1),1)

You see the potential problems, right? First, what if I can't think of 75 items? I might only have 50 in the list. But if I add more items, I don't want to have to change 24 cells to modify the '75' to the actual number. Yes, I can use the 'Replace All' edit function, but I can set up an Automated Process instead, which is always better. So I embed a Count function to automatically find out how many items there are:

The bigger problem is that if I have 24 cells with this INDEX function in it, there's nothing to satisfy Requirement #6, where no duplicates are allowed. So I have to figure out how an Automated Process can randomly select from a list and make sure that it doesn't select the same item twice.

Perhaps there's a really slick way of doing this using recursive functions, but my Excel-fu is not up to that. My solution came to me when I visualized the process as a set of 24 selections, each taking from a list that has all the previously-selected items removed from it, like this:
List #1        List #2        List #3        List #4

1  Coffee      1  Coffee      1  Coffee      1  Coffee 
2  Internet    2  Internet    2  Internet    2  Dishes
3  Walk        3  Lunch       3  Dishes      .
4  Lunch       4  Dishes      .              .
5  Dishes      .              .              .
.              .              .              72 Comic
.              .              73 Comic
.              74 Comic    
75 Comic    

Pick: 3 Walk   Pick: 3 Lunch  Pick: 2 Internet  etc.
One can do this easily by having the A column in the List worksheet be the full list, and columns B through Z be derived from that, where each cell checks to see whether it should be populated by the one from the left, or the one left and down, depending on the number selected for that list, kind of like this, where Card!$H$3 is the randomly selected number, cell $A2 is the Item number, and this formula is for Cell D2:

Or if you're like me and don't like seeing empty cells be populated with zeros:


Yes, this is a bit of a "Brute Force" method, as it builds 24 lists, but we're only creating formulas in a couple thousand cells, and computers are very good at performing repetitive calculations quickly and accurately. Plus, we can look at each list and validate that the spreadsheet is selecting the proper entry and dropping it from subsequent lists. There's a lot to be said for easy validation.

Speaking of validation, it's time to do that! How do we know that our process is performing correctly? By comparing our results to the requirements defined in the Definition phase.** Here we go:

1. Create Bingo cards with items randomly selected from a list of 75 items. - Yes, the end result is a Bingo card randomly filled with items from the list.
2. Each card has 25 items, in a 5x5 grid. – Yes, there are 25 items in the 5x5 grid.
3. Of those items, 24 are randomly selected. – Yes, 24 are randomly selected (we consider Excel's RAND() function to be adequately random for our purposes. We'll leave the discussion about the quirks of random number generators for another time.)
4. Center square is FREE (and described as "Watching Funny Cat Videos"). – Yes, the center square has that text in it in every case.
5. Any given item can appear in any row or column (except the center square). – Yes, each entry can select from any item in the current list (Unlike traditional Bingo cards, which only allow numbers 1-15 to appear in column 1, 16-30 in column 2, etc.)
6. Items cannot appear more than once on a card. – Yes. Comparison of each of the 24 lists shows that once an item drops out, it can't be selected again.

So we're 6 for 6! Success!

In a customer project, we would now perform User-Acceptance Testing, where the project sponsor or delegate would review the results and complain about how it's not what they expected***.

So, in that handy example, you get to see both the kind of work I do, AND how I waste my Free Time. Now if I actually get my act together and run a game of Evan Weekend-Activity Bingo, you can say "Hey, I know how you created those Bingo cards!"

Thanks for reading!

* Heh. A little inside joke there. Many people seem to believe this, and build new applications for everything. I've always maintained that sometimes a low-tech solution is the best answer.

** I usually favor automated processes for validation, where possible, as they tend to be more thorough and accurate than visual spot-checking. Of course, setting up an automated validation process is a mini-project, with definition, coding and validation phases of its own.

*** Hahaha! Just kidding! Well, they DO complain, but not ALWAYS.
  • Post a new comment


    Anonymous comments are disabled in this journal

    default userpic

    Your reply will be screened