So why spreadsheets for calculations and not MathCAD/SMath type applications? *{Sorry! I didn’t link to MathCAD because it doesn’t have its own website, and who owns it and is making money from it changes from time to time. I have better things to do than tracking down and updating links to commercial websites.}*

MathCAD like applications are expression evaluators, which either use numbers or named variables. If a number is not assigned to a variable name, then that number cannot be referenced. Most science and mathematics subjects use single character variable names, the Roman alphabet has 26 different characters, 52 if we include upper and lower case. To extend the number of variables subscripts are used and various of accent like symbols, and the Greek alphabet is used. However, the Greek alphabet has a tendency to look like scribble compared to the Roman alphabet and not all that conducive to learning and understanding anything. Also letters of the greek alphabet may be used as special prefixes, as a kind of function or operator: for example delta tends to be prefixed to mean difference (deltaX = X2 -X1). Now sure I could look up the characters to get the letter delta into the previous text: but that requires extra time, and if the symbol was there, how many people would know it is known as delta. As more letters of the Greek alphabet are incorporated into an algebraic expression the more obscure and meaningless it becomes. Thus the mathematical type of textbooks is not the most sensible way to express an idea. So whilst single character variable names are compact, they lack real meaning. For example in mechanical design I use the Greek symbol sigma for stress, but also use the symbol sigma for standard deviation. If I wish to do material testing and calculate the standard deviation of the stress data: what symbol should be used for stress. Ok! For civil/structural engineers the symbol for stress is ‘f’, but only because the traditional printed ‘f’ is an elongated ‘s’ with a cross through it (eg. stress=ftreff : which once again requires extra time to find an appropriate font to display, but cannot guarantee it will display correctly on the reader’s machine).

So to be meaningful we don’t, and shouldn’t really be using single character variable names, and certainly shouldn’t be using names as stupid as M*, as is used in the Australian Structural codes. So could use variable names like ‘stress’ and ‘stdDev’: but is the abbreviation for standard deviation really meaningful? Then we get to the issue that I have more than one stress value to calculate, this is maybe formally resolved by using matrices. But not every collection of values forms a valid matrix, on which matrix operations can be used. So programming languages instead make use of arrays, which are more flexible.

If working on a parametric model for a large system, then dreaming up unique variable names can become a real pain in the head. Also taking into consideration that most formal matrices are full of zero values, then being forced to use matrices could be a waste of computer resources: depending on how the software actually implements the matrix behind the scenes.

Thus rather than MathCAD like applications being a benefit to calculations, they are a major hindrance.

Spreadsheets on the other hand comprise a matrix of cells. Depending on the implementation the cells may actually be something like a linked list of used cells *{As I remember the benefit of QPro over Lotus 123, was the improved memory usage and speed of calculation. QPro used a linked list of used cells, Lotus 123 used the block of cells in the used range many of which weren’t in use.}.*

With a spreadsheet I don’t have to dream up variable names, because every cell is already a named element of a matrix. I can carry out calculations in a tabular format or in a more freeform format. I can name ranges if I wish, though it would be better if I could name a table of data and refer to the row and column indexes of the named range *(eg. it would be good if could use: Moment[1]=Force[1]*Distance[1]).*

A spreadsheet is like a large piece of paper on which the traditional designer scribbled sketches and calculations. Though sketching in MS Excel is something of an hassle, but its a hassle in most calculation packages.

Which raises the next issue: most calculations are to aid in the specification of physical components which have dimension and geometry and are otherwise described by drawing in CAD. When drafters automate CAD they tend to create input forms requiring dimensions and geometry which are the output of ‘engineering’ calculations. The input forms to ‘engineering’ have fewer dimensional parameters, therefore the input forms for producing the drawings could be simplified if the so called ‘engineering’ was done behind the scenes. Cutting out the transcription from one operator to another, not only reduces potential for errors but also makes the process faster.

However, making things faster is not always a good idea. Engineering calculations going straight to machine tools, or 3D printers, changes the activity from generating waste paper to generating large volumes of waste steel or waste plastics. Ok! for CNC machine tools there are special reusable metal alloys and machinable waxes: though need energy to cast back into useful workpieces.

Ok! So it would appear need to get the calculations right. Not entirely so, because the results of calculations do not always feed directly into specifications, or bills of material. For example a beam maybe 4.5m long, whilst the available stock materials are choice between 3m or 6m stock length. So whilst we need to make the beam 4.5m long, we otherwise have to purchase and handle 6m lengths of material until it is cut to size. Similarly the diameter of a steel shaft may calculate at 23.4 mm, but the options available are 20mm or 25mm.

In these situations we can either use trial and error and pick the stock material/component, and check if it is fit for purpose, and choose something else if it isn’t. Or we can calculate the requirement, and then look up the nearest available component from the available stock.

The drafter producing the drawings and other specifications, simply wants to know the dimension which meets all the required performance criteria: they don’t need to know the calculations which occur behind the scenes. They can select options from drop down lists, and if the selected is not suitable for purpose, it can display red, if it is suitable for purpose then it can be displayed highlighted in green.

But we want to see the calculations behind the scenes, or do we?

As I have mentioned before the people working for the regulatory authority are meant to be conducting an independent assessment of whether the specification for a proposal describes something which will be fit-for-function. The review is not an arithmetic check of the designers calculations. Furthermore the designers calculations are not a guide for the reviewer.

So the following software may produce nicely illustrated and readable calculations:

However I would tend to disagree with the premise posed by Pensolve. I would contend that if regulatory authorities need readable calculations, then the people they employ are not doing their job properly. The person carrying out the review should be creating their own assessment tools. The reason there was a problem with nail plated roof trusses, is because the industry produced tools for rapid design of the trusses, but the certifying authorities failed to do likewise. If you are routinely checking and certifying trusses or any other manufactured structural product, then you should have automation tools as well developed as those used by the manufacturers.

I would also contend that promotion of MathCAD like software is based on misleading information. There is no direct relationship between the displayed mathematical expression and the numerical evaluation. The algebraic expression has to be translated to get a numerical result. Also behind the scenes the software is likely no different than a spreadsheet: a linked list of cells or list of named variables. Such linked list either represented by an indexed array or the use of pointers: all of which is highly divorced from the original mathematical expression. As I point in a previous article, what you see isn’t necessarily what you get. So if you are just reading the pretty mathematical output of MathCAD and like software, then you are not doing anything of value.

As I have said, calculations are a means to an end, not an end in themselves. I have further indicated that engineering takes place at the frontiers of science and technology. There can be no calculation templates for work at the frontiers, no one has been their before, therefore no one knows what needs to be done. At the frontier the calculations are the documentation, the first textbook on the subject matter.

Typically, we are not meant to be doing engineering, we are supposed to be carrying out routine calculations to reach defensible decisions. If something fails and that something was built to the specifications, then the specifications were wrong, and if the specifications are wrong then the calculations were either deficient and incomplete or in error.

As I have mentioned many times, the calculations for the bolted moment connection of cold-formed sheds is not fully specified because the calculations are deficient. The calculations used by manufacturers can only be considered as a feasibility study: namely a portal frame shed from cold-formed c-sections is viable. Whilst there have been few reported failures, it doesn’t mean the sheds fully comply with the codes of practice: it doesn’t mean they are entirely fit-for-function.

But here we have a secondary problem with the regulatory approval process. Regulatory approval is not meant to be a check of detailed design, only a check of that which is covered by the regulations, and regulations are not meant to cover everything. Now, if the only thing that manufacturers and builders care about is getting city council of their back, and the only thing council is concerned with is code compliance. And similarly if code compliance is the only thing that the new breed, of so called, ‘engineers’, care about , then we have a serious problem.

This new breed seems to think if it is not covered by code of practice then its not important. They have their ticket to employment and that is all that matters. If they weren’t taught it, then it doesn’t matter, its someone elses problem. This new breed would have never generated the scientific and technical knowledge which we have today: and they are unlikely to generate any further knowledge. But that’s for another article.

Calculations have a purpose and we need to be able to present those calculations in multiple formats for different purposes. Furthermore if one person can get the results in 5 minutes, then as a reviewer you also need to be able to independently get the results in 5 minutes. Which leads onto another issue, it is unacceptable that LimSteel is basically the only software for steel design to AS4100. Such market monopoly lacks independence. Likewise it is not acceptable to design a frame using say MicroStran and then review using MicroStran: for an independent review the analysis needs conducting using different software.

On the other hand the first stage of reviewing the results of a 3D frame analysis using MicroStran is a pocket calculator: and you don’t look at the scrap paper spewed out of the software, you look at the the specification drawings. Review is a progressive incremental refinement of assessment modelling until reach a decision that cannot fail the proposed structure.

Apparently I break every structure I look at, leading my father to refer to me as “conan the destroyer”. This is typically a result of conducting twice the number of calculations others are doing, then I need around 5 times the number of calculations to scrape a manufactured structural product back into compliance with a minimum of modification.

So I take report example 103 as a benchmark to be improved on: Example Reports and Calculations.