No one is invincible!

Spreadsheets are great.  We all use them to organize our thoughts and experiment with what-if calculations.  In talking to different engineering companies in the Boston area, we have realized that spreadsheets are being used to do real engineering.  Sometimes, a spreadsheet is the perfect tool for our needs.  Other times, it starts to grow until we realize that we have surpassed the limits of the spreadsheet.

The examples of exceeding the limits of the spreadsheets tend to come in two forms.  Either, the spreadsheet is being used as an ad-hoc database or it is being used to perform a relatively complicated multi-step calculation that might be better served by a script or a program.

When the spreadsheet is being used as a database, there are a few clues to when it is time to consider moving data into a more powerful tool.  At a high level engineers will be better served by a simple database in place of the spreadsheet if any of the following are true:

• If the spreadsheet has multiple authors
• If data searching is required
• If it has a combination of calculations and data storage
• If it requires different numbers of different types of data

Similarly, there are a few clues that an engineer might look for that indicate that his spreadsheet would probably save time and money if it were in the form of a simple script for computation:

• If the same calculation is replicated on different worksheets in order to consider different starting values
• If many versions of the spreadsheet are saved with special names to indicate the what-if case that is being considered
• If the data are being selected from indexed lists of pull downs

Migrating ad-hoc databases and multi-step calculations out of excel and into other tools or scripts is usually pretty easy. 

In some cases, useful excel functions are leveraged that a user does not want to re-program or replace from standard libraries.   Most scripting languages offer ways to access Microsoft excel functions directly.  Visual Basic, Ruby, and Python all have such extensions.  In linux, there are also C libraries for the open-office platform that can be used.

This entry was posted on Saturday, December 20th, 2008 at 12:56 pm and is filed under Engineering Practices. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.