Spreadsheets vs. Worksheets for Engineering Calculations

I first learned to use a spreadsheet for engineering calculations in college. In those days we used Quattro Pro for DOS before windows applications were common. Spreadsheets provide a lot of powerful features for engineering calculations such as advanced engineering functions, good data organization, custom functions and macros, and graphing capabilities. With a strong foundation in using spreadsheets to solve engineering problems in college and all of the advanced features, spreadsheets become a natural choice for engineers for doing calculations.

Spreadsheets have some major weaknesses when it comes to engineering calculations though as pointed out in [1] and [2]. The author is a little biased being the CEO of Mathsoft, the maker of Mathcad, a software application for engineering and scientific calculations. The major weakness is that spreadsheets hide the formulas, algorithms, assumptions, and methods. These things are hidden behind the data which is shown in the cells. The formulas, algorithms, assumptions, and methods are actually much more important than the data itself because they are what can be reused on future projects and understanding of these is what is needed to make modifications and upgrades to existing products. Spreadsheets thus make engineering reuse and maintenance very difficult. The second major weakness is actually a result of the first. Since spreadsheets hide the formulas, algorithms, assumptions, and methods they are very prone to mistakes and errors. This can lead to problems in design quality.

In my personal experience, I have used spreadsheets to perform timing analysis on digital systems. One such spreadsheet for a DDR SDRAM interface actually had an error in it that went undetected through a few projects until that error caused a false timing violation on a project that forced the engineer to investigate further. It turned out that this error was not significant enough to cause negative timing margins on the previous projects, but this was pretty lucky.

I have also tried to create signal integrity calculators using spreadsheets. They are difficult to create because many calculation steps have to be worked out on paper by hand and then converted into spreadsheet formulas. The formulas are then hidden and cryptic, and so to fully understand the calculations, the same steps have to be worked out by hand each time. They provide quick answers, but engineering is more than plugging numbers in and getting answers.

An alternative to spreadsheets is worksheet based calculations as provided in the Mathsoft Mathcad tool. This tool allows both symbolic and numerical calculations. Equations are laid out symbolically just as you would write in a notebook or on a whiteboard. It is very easy to document the sources of equations and assumptions with annotations directly on the worksheet. I have started to use the tool for digital engineering calculations such as timing analysis. I have also used the tool to symbolically implement error correction coding algorithms to determine how much combinational logic will be needed to perform certain functions.

Using worksheet based tools such as Mathcad requires a different mind set than using a spreadsheet. It is probably easier overall, however with so much experience using spreadsheets it can be difficult to switch. I am excited about the benefits of worksheet based calculation. I will document tips and techniques as I stumble upon them and I encourage others to post their own tips and techniques for using these types of tools.

This is not to say that spreadsheets do not have a use in engineering. They are very good at managing and displaying large amounts of test or simulation data and managing bill of materials. Some tasks are best done with a combination of spreadsheet and worksheet. Mathcad allows spreadsheets to be embedded directly in worsheets with links between the two. I have found this to be very helpful, especially when displaying a summary of results. For example, you do all your calculations in the worksheet and then you pass the results of the calculations into a nicely formatted spreadsheet showing the results in a table form. Another example would be a power budget where tablulations of power consumption by device could be listed in the spreadsheet and then more advanced calculations such as conversion efficiency could be performed in the worksheet.

Spreadsheets clearly have a lot of useful features, but their drawbacks outweigh those features for many engineering calculation applications. I think spreadsheets are way over used by engineers. Engineering colleges would do a great service in teaching both spreadsheets and worksheet based calculations for solving engineering problems so that engineers could learn early on the power of each type of tool.


[1] Randles, Chris. March 7, 2005. "Spreadsheets: often do not add up." Design News Issue 4.

[2] Randles, Chris. October 2005. "Spreadsheets just aren't for engineering." Product Design and Development p 24.