Research, evaluation, analysis

More Power for Your Spreadsheet
This week I'm just offering a brief tip about a problem I have noticed in many spreadsheets I've run across. It is the entry of multiple codes in the same field. You're probably wondering what that is, so here's an example.

Let's suppose you're maintaining a personnel database in which you have a record for each employee in your division. For each employee you record the training courses they have taken. There are three courses they can take, and you have decided to identify them as courses 1, 2, and 3. How do you enter the courses employees have completed in your spreadsheet? For example, if someone has completed courses 1 and 2 but not 3, how would you enter that information?

I can assure you that many people would enter the two courses in one field of the spreadsheet. That is they would enter a string like "1,2". Well, that's not the most effective way to do it.

The problem is that you cannot sort the file effectively. For example, if you want to find all the people who have not taken course 2, any sort you could perform would still require you to hunt through the spreadsheet for relevant records and keep a running total on paper. That's not why we have computers. If, on the other hand, you enter completion of each course in its own separate field you can just sort on the relevant field and all the records you're looking for will appear together.

What you would do is assign a separate column for each course. I prefer to use numerical codes in this sort of column, simply because numerical codes take up the least space (and require the fewest keystrokes). So every time a new employee was entered in the spreadsheet you'd put a 0 (zero) in each of the three columns. Whenever an employee completed a course you'd change the 0 to a 1. To find out who still needed to take the course, you'd sort the column and they would become the first set of records.

I have seen this problem in spreadsheets intended for similar purposes selection from waiting lists, for example. The problem actually renders the information worthless. If the information is entered correctly, the spreadsheet can actually simplify selection. If the information is not entered correctly, the spreadsheet just becomes a very expensive substitute for an index card.

More Power for Your Spreadsheet © 1999, John FitzGerald
Home page | Decisionmakers' index | E-mail