Using Spreadsheets for Engineering Design

The spreadsheets I have released both here and over on ExcelCalcs are all in MS Excel 2003 format, and in the main are dependent on an MS Excel spreadsheet add-in (.xla). As a consequence of this “add-in” the spreadsheets are not usable in OpenOffice or LibreOffice.

Further as a consequence of the hardware failure of my Windows XP computer and restrictions of  OEM licensing, I have reluctantly moved over to Windows 7 and MS Excel 2016. And As I didn’t like Windows 7 and given that most of the commercial software I have doesn’t run on Windows 7, I figured I’d may as well accept the Windows 10 upgrade. The experience has reinforced my view that we should all develop our own technical tools. Whilst I still wouldn’t go down the path of developing our own CAD package or 3D matrix structural analysis package with graphical user interface. I believe that with open source software, such capability is becoming increasingly viable. Another issue which adds to the viability is increasing automation, and increased use of computers to auto-generate products, for example structural form based on rules. This automation tends to lead to duplication of effort, especially in defining data structures and commands, so eventually reach the stage where have to ask: “Why am I automating this application? What is the chunk missing from my own program which makes it dependent?”. The primary reasons for using MS Excel or any other spreadsheet is that it removes the need to:

  1. Define a data file format: and write procedures to write and read the file.
  2. Write input forms to create the data and an editor to modify the data.
  3. Write a means of previewing and printing the data and results.
  4. Write a means of searching and browsing data and results.

One benefit of a spreadsheet over the likes of MathCAD / SMath Studio is  that there is no need to dream up a variable name to represent a value: every cell of a spreadsheet is already a variable. Every cell is like a memory store on a pocket calculator. A column of cells is like the stack on an HP RPN calculator, except that every element of the stack has two adjacent elements, one which can be used to label the value, and another to indicate the units of measurement. A spreadsheet can be used like a scratchpad to do calculations, where can point and pick values on an as needs basis: the presentation however can be modified to suit the situation. Some times want to present the detail of calculations for others to read, and still at other times just want to get straight to the numbers, possibly a specific set of numbers compared side by side with out all the intermediate calculations cluttering up the available screen space.

When I first started using spreadsheets I wanted to present calculations similar to how I wrote them on paper:

  1. Show the variables and known values
  2. Conversion of values to preferred units
  3. Show the algebraic expression
  4. Show the numbers substituted in algebraic expression
  5. Show the result

To achieve this I wrote the cell formula and then  copied the cell contents into another cell and wrapped string functions around the values. It is a time consuming exercise and time wasted on presentation which delays getting to the numbers required.

My dad on the other hand came from programming HP 65 calculators, where the primary task was to carry out complex sequence of calculations and get results to be included in hand written calculations. His view was that most structural calculations were simple enough to do by hand and only need a computer for complex calculations.

My view was that we used HP RPN calculators so that the result of previous calculation was available for next calculation, and by doing so we reduced round off and transcription errors. But there was still a potential for transcription error from calculated result back to the report. By using a computer, a simple sequence of calculations can be automated along with the presentation of the report. By the use of the computer we could improve the consistency of the calculation process and also increase the detail of the calculations. We could build on the calculations from one project to the next, and at the same time reduce the amount of time  taken to perform an increasing number of calculations. Further, even though the printed calculations may lack some of the formatting of handwritten calculations, in the main they would be more readable than much of the handwritten scrawl, and with the passage of time the formatting could be improved. For example can now use ExcelCalcs XLC to format presentation of calculations in MS Excel. However my general view is not to go down the XLC path, once upon a time when I started with spreadsheets that is exactly what I wanted.

Back around 1996 my dad had about 3 Quattro Pro for DOS spreadsheets for cold-formed steel design to AS1538. It took me several weeks to combine these worksheets into a single QPro for Windows workbook, and collapse down to a single worksheet and otherwise extend to allow for combined tension and bending which was not covered by the code. I was told the latter didn’t matter because lateral buckling controlled bending, I argued that the dominant design case for cold-formed steel sheds was combined bending and axial tension due to wind uplift, and it was possible to over stress the tension flange of the section. I was told to: ” go figure  it out then”. Which I did, and a new presentation was created for our cold-formed design spreadsheets, which became the basis of the spreadsheets when I eventually converted from AS1538 to AS4600. The important issue here, however is that it took me several weeks to become familiar with both AS1538 and my dads spreadsheets, and figure out what the spreadsheets were doing: as the spreadsheets were simple calculators not presentation reports. My first intention was to modify the spreadsheets so that all mathematical expressions were presented with all values substituted. However the results of such vertical presentation of equations and results, If had pursued that approach, would have been a significant increase in the number of pages for the calculations. As it was, one page of calculations were required for each segment of a beam being checked. The question then becomes what value are these extra pages? The simple answer is no value: so why expend the effort creating them?

This answer was reinforced when I started assisting with structural reviews for private certification. I would do preliminary review of structural calculations submitted and identify the problems I thought existed, my dad would then complete formal review. For many of the situations I couldn’t read the handwritten calculations. Others I couldn’t follow the logic of the calculations, and others I considered the calculations to be deficient or the calculations not to be following the intent of  the code.

I was advised that irrespective of the submitted calculations what did I think about the structure on the drawings and in the written specifications? If I was designing it what would I specify for the members and connections? What answers did my spreadsheets give? For example it doesn’t matter if my approach to applying local pressure factors to purlins is different to the designers. If I was designing the purlins would they be the same? In that particular case the answer turned out to be yes.

 

to be continued …

 


Revisions:

  1. [24/09/2016] : Started Writing
  2. [25/09/2016]: Published