5 reasons to choose math worksheets over Excel spreadsheets for structural calculations

Although there are plenty of software nowadays, engineers still have to develop custom calculations to solve specific problems. Some may still make them “by hand”, but most engineers use some kind of software for that.

The problem is that in most cases, we have to document our calculations and collect them in design reports or calculation notes. And it would be also very good, if we could give them professional look and feel. The other issue is to make the calculations reusable, in case we have similar projects in future.

Apparently, a peace of paper and pencil would not do the job anymore. Even a simple hand calculator (except for quick estimates). What we need is a suitable software for that purpose, but what?

To my experience, the most widely used solution for custom engineering calculations is Excel. At the same time, it is probably the most inappropriate for the job. But it is not actually an Excel fault. In general, it is a great software, very mature and high quality, but for tabular data. I myself usе it quite a lot for bills of materials and construction cost calculations. Spreadsheets are the best for simple formulas that have to be repeated on multiple rows/columns.

However, structural design algorithms are tree-like in nature, not tabular. Even if a tree data structure can fit in a table, this is not the optimal solution. It has some issues that cannot be neglected. During the years, I had to write a lot of structural design spreadsheets on Excel, as well as math worksheets on software like MathCAD or Calcpad. Over time, I found some important reasons to prefer the math approach over Excel spreadsheets:

1. Formulas are built by referencing cells.

It is slow to write and difficult to check and follow, especially for the longer ones. “M = FL + qL^2/2″ is much easier to type and more readable than “= D2A3 + C1A3^2/2″. It is like obfuscation of the actual formula. For longer calculations, it gets even harder. Imagine that you have 1000 lines (rows) of calculations and your formula uses some cells at row 1, 500, 200, 700, 900, etc. You will have to scroll up and down multiple times to find the required cells to be referenced. Of course, you can use named cells, to improve readability, but it requires additional work.

2. Formulas are hidden inside the cells.

That makes the printed report a real black box for the checking engineer. There is an option to make them visible, but without formatting they are difficult to read. The best option is to write them once again with MathType. Besides it is a double work, you can mistype something and there is no guarantee that the formulas you see, are the same inside the cells. Also, variable substitution in formulas is not available in this case. So, Excel spreadsheets are prone to errors.

3. You cannot apply units of measurement to values and use them in formulas.

You can write units of measurement in the next cell, but you cannot attach them to values and they will not take part in your calculations as it happens in math software. So, you must include the proper conversion factors manually in all of your formulas. Otherwise, you will simply get incorrect results.

4. It is difficult to define your own custom functions.

You must use VBA programming, which is not an option for a newbie. In math software, this is a piece of cake. You can simply type f(x) = 2*x^3 – … Also, it is very easy to plot a function graph directly from its equation. In Excel, you must calculate it in multiple cells and then, plot the respective range. If you have discontinuities or infinities and if you are not quite familiar with the function properties in advance, you can easily miss them and end up with incorrect graph.

5. It is almost impossible to branch the solution path and the report contents.

There are a lot of cases in structural design when the solution continues in completely different ways, depending on some intermediate checks. In systems like Calcpad, you can just add if-else if-else-end if condition blocks and put different formulas, text, and images in each one. In Excel, you can achieve limited results with intensive use of VBA.

So, even if you are an experienced spreadsheet developer and user, it is worth to give the math approach a try. Calcpad is perfect for this purpose, because it is lightweight, fast, easy to learn and free.