None of the spreadsheets I have uploaded to
ExcelCalcs make use of
XLC . I like XLC, but after some 20 years of creating spreadsheets without the benefit of XLC, I am not about to start a massive exercise of converting my spreadsheets. More over I have a developed a different approach to producing calculations. When I started I did want to present calculations in similar manner to how I wrote out calculations with pencil and paper, and so every now and again I would take a look at
MathCAD, and then change my mind about the whole thing. I also read a white paper by MathCAD which identified a problem of design solutions being scattered between handwritten calculations, Fortran source code, spreadsheets and the likes, and how this was not otherwise readable by all.
However for the authors of MathCAD, calculations are an end in themselves, not a means to an end. As I keep pointing out, real engineering takes place at the frontiers of science and technology. Calculations as an end in themselves are important for real engineering, because such calculations document the design-solution, how to get from concept to reality. There are no national standards, no industry handbooks, no established technical science. The engineers calculations will define the technical science and the foundations for writing future standards and industry handbooks. But this is not the environment in which the vast majority of modern professional engineers operate.
Most so called engineering, is not what I would describe as engineering, it is technical design based on firmly established technical science, making proper use of national standards and industry handbooks, for the purpose of applying and adapting established technologies. The community will not tolerate failures or defects in such established technologies. It is therefore important to get the calculations correct, but it is also important to get the calculations done quickly and efficiently.
The regulatory system typically requires documentation to be issued in at least triplicate. The more pages of calculations the more paper to print off and the more documents to store and otherwise manage, and mostly for no real benefit to anyone. When I look at the MathCAD templates, of
TEDDS, Master Series
PowerPad,
EnerCalc to name a few, all I see is software that produces pretty calculations and potentially a large pile of scrap paper. Sure we can now print the calculations to pdf file and therefore save the paper and the forests, but the pdf files consume hard disk space, and someone has to read through and check the content. The question is why, does all that documentation need producing and checking?
Here in South Australia we now have a
ministers specification for structural software used by persons who are not engineers. It is largely a result of failures of nail plated roof trusses. These trusses are typically "designed" by timber estimators working for truss fabricators using proprietary software. The South Australian regulations require independent technical checks for development approval and obtaining building permits. The software can design a truss in a few minutes, the reports traditionally produced by the software were relatively deficient. No certifying engineer had similar tools, therefore they could not independently check and relied on information in the reports to make some checks.
The first problem is that analysing a truss is time consuming, the second problem is inadequate information is provided with respect to connection details. In particular whilst the software may have sized the nail plates required at the connections there was little evidence, that any checks had been made that such nail plate would fit, and that there was adequate timber to fasten into. For shallow trusses this can be a problem, and really need to draw the connections out. The old design manuals had templates which could be over laid on drawings to check for fit.
There is an increasing number of structural products in the market, and suppliers wanting software to do the engineering, and therefore some controls have been put in place for this software.
However, my view concerns the certifiers. Nail plated roof trusses have been around now for more than 30 years, and the certifiers still haven't got rapid design and/or assessment tools. I contend that in this age of personal computers and electronic calculation pads, it is nonsense that only the big truss companies can afford to develop rapid design software. I also contend that it is unacceptable that there often no alternative software for proper independent checks. For example
LIMSTEEL basically holds a monopoly for hotrolled steel design. Not only is it integrated into
MicroStran and
SpaceGass the two main frame analysis software applications in Australia, but in modified format it is also the basis of
ASI design capacity tables (
DCT). Whilst this produces consistency, its lacks independent assessment, no alternative checks and balances, and that is not a good thing.
In the past our office here, has carried out independent checks. Initially I looked through hand written calculations submitted and tried to follow them. Often difficult as sometimes the calculations are just numeric expressions, with no algebra and no descriptions as to what is going on. One set of calculations I checked kept moving from limit state to permissible stress using a factor of 1.5. The calculations also involved spans of 1.5m and load widths of 1.5m . With no explicit description of which each calculation was for, they were difficult to read.
Other calculations, I had different views on application of local wind pressure factors. At the end of the day however the design engineers calculations are irrelevant to the certifiers independent assessment. Why would a certifier waste 5 hours trying to decipher the design engineers calculations when they can use their own software to check the specifications and drawings in 5 minutes?
Which reversed the situation for me. Normally my view was, if I can complete the calculations in 5 minutes then I expect the certifier to be able to check in significantly less time. As designer I have to go through various alternative solutions, or seek a solution through many iterative trials and errors. The certifier only needs to check the validity of the final solution: and either accept or reject.
If I can produce software then the certifier can equally well produce similar software, and has far greater justification for doing so: they have more pressure to check in short time frames, and also greater recurrence of common design solutions.
If a supplier of structural products phones a consulting engineer up to get an estimate of member sizes, so that they can quote a realistic price for supply: that answer needs obtaining quickly. Preferably in 24 hours if not 1 hour. A week is likely the upper limit and not typically acceptable.
The calculations, to AS4600, for checking a segment of a cold-formed steel section are fairly extensive. One engineer who produced calculations by hand and moved over to using MathCAD increased the thickness of his reports by about five times: it served no real purpose and was potentially less readable. If produce calculations by hand then there is greater tendency to be more efficient and reduce repetition. If use a computer then there is a greater tendency to opt for flexibility. The problem with flexibility is it increases the potential for error on each project. Some times it is just far more efficient and reliable to have a custom tool made for the job, rather than have multi-purpose tools. When it comes to computer software it is often possible to customise multi-purpose tools for a specific purpose, but that also tends to be an expensive approach.
From a mechanical view point, it wouldn't be sensible to customise a CNC flexible machining centre to make bolts all day every day. It is far more efficient and less expensive to design and build a specific machine for making bolts: such machine would also require resetting less often.
Similarly with software, custom design tools are more efficient than multi-purpose design tools. Microstran and Spacegass have hardly changed since they were ported from MS DOS to MS Windows. There are many suppliers of structural product who want custom frame analysis software, but go to small consultants who don't have any such software to use as a foundation for building custom software products. The software then becomes time consuming to produce and also expensive.
The other problem is the software becomes proprietary and the consultants also get their hands tied and prevented from supplying similar to other suppliers of structural products. Due to the problems with the nail plated roof trusses I don't believe such mode of operation is acceptable. A variety of software tools need to be available to manufacturers, builders, designers and certifiers. Further no certification should be based on using the same software as the designers used. It is not all that sensible to jump on the upgrade band wagon: the upgrade may introduce errors which were not previously there.
So calculations have different audiences and different purposes and appropriate tools need to be adopted for each combination of audience and purpose. Mathematical type face or text book presentation of calculations has one purpose: but it is not necessarily more readable. Personally I find programming source code more readable than some of the symbolic mathematical notations. Some times concise and succinct can become obscure and meaningless. Why use the Greek symbol sigma for stress and standard deviation: especially if conducting statistical calculations on stress? However in programming need to balance between clearly identifying meaning of a variable versus unreadable over long mathematical expressions.
However the two main problems with programming, concern input and output. The calculations transforming the input into the output is usually relatively easy. Spreadsheet software has already solved the problem of data storage, file format, and editor to get data into the file, and the means of presenting results and sending to a printer. All the user has to do is create their own report in their own style.
One style would be to move from the top of the screen to the bottom, showing algebraic expressions, numbers substituted into those expressions, and the final calculated result: the use of XLC would help with such presentation.
The use of a spreadsheet however combines transforming calculations and vba source code with the data, and is not very efficient use of computing resources. It is easy for just about anyone to set up, but otherwise extremely wasteful.
Calculating sectional moment capacity (phi.Ms) to AS4600 involves a large number of intermediate calculations, and calculating member moment capacity (phi.Mb) involves even more. These calculations need repeating several times for different segments and load cases of a single member, and again for each member in a project. My original AS1538 (permissible stress version of AS4600) required a single printed page for each segment. If all the algebraic expressions had been included it would probably stretch to 5 pages, as it was I thought one page was too long. Noting that I also had to photocopy each page and bind into a report. so being able to check multiple segments, multiple load cases and multiple members on a single page: was something of an important objective.
There is also an issue of how spreadsheets are used. My original spreadsheets were centralised. Use the spreadsheet on a project and print the results out. If results were saved, they were over written by the next project. There was no copy of the spreadsheet for the project. Also had different spreadsheets for different codes and each of these linked into larger spreadsheet for specific structural forms. So if changed the modules, the results from the larger module also changed, and that also meant low potential of repeating the results obtained on previous projects. But hard disk space was in low supply, so it kept disk space consumption low.
The linking of the spreadsheets is also another issue. Initially just use different spreadsheets for different dependent purposes and manually type the results from one into another. Whilst that provides flexibility with many small tools adaptable to a variety of structural projects, its not very efficient and can result in transcription and round off errors. It is far more productive to have a spreadsheet which does all the structural calculations for a given structural form or whole structural project. But that can cause revision problems for common pages of calculations across each project type: any error has to be fixed in multiple locations rather than one central location.
Another problem with using spreadsheets is the use of common data such as material properties and section properties. A simple structure like a cold-formed steel shed, involves several structural members: columns, rafters, mullions, girts, purlins, struts, bracing. Bringing all the section property data into a spreadsheet can be cumbersome, as is referencing the correct collection of such data. Using a database is more efficient than using a spreadsheet, and MS Excel permits access to MS Access data using vba and DAO. Using DAO the spreadsheet only needs to know the section name such as C300-30, with that vba can get all the data and calculate phi.Ms and return the value directly to the worksheet: there is no need to show all the intermediate calculations.
Whilst it is important to be able to see all the intermediate calculations if something goes wrong, it is not necessary to display all such calculations for each and every project. It is cumbersome scrolling back and forth through long tracks of calculations, to change input parameters and see final results. Whilst it is possible to split screens, freeze panes or open new windows and tile: it is still using more screen space than really necessary to get the desired results.
Compared to many industries where the technology would not be possible without engineering design, the building industry has had engineering imposed upon it. Buildings were constructed for 100's if not 1000's of years with out the use of engineering calculations. The regulatory imposition causes delay in supply and often achieves no real benefit.
Sometimes the more detail submitted to regulator the more rigorously they check the design, and the more questions they ask. Send less in they make fewer checks and ask fewer questions: too little and they ask lots of questions. Other occasion's however get the impression that the more extensive the documentation, the less likely any checks occur. The regulator just looks at the pile of paper says, they must have done a thorough check: and immediately approve. It is thus difficult to assess whether should provide more or less detail in the calculations.
Therefore need a certain amount of flexibility in automation of calculations with ability to create multiple presentations of the same calculation process. Spreadsheets with application programming language such as MS Excel and Openoffice/Libre Office provide such flexibility.
If I write visual basic for applications (vba) user defined functions (udf), I can easily develop MS Excel workbooks which use the worksheets for collecting, storing and presenting information, or write an application which just uses the workbook for storing data, and using dialogue boxes for collecting and displaying information. I can also move away from MS Excel altogether and write stand alone programs.
Further more I can build on the vba code and further hide information. For example I want to know what phi.Ms to make a decision, most other people don't care, they just want to know if a given structural section is suitable for their needs.
So engineers preferred presentation of calculations largely stems from a tradition of working calculations out with pencil and paper. If using a computer to carry out the calculations, then such presentation is no longer overly relevant to the majority of people who need to know the results and conclusions reached from such calculations.
To enable and empower people to get on with making and applying established technologies, we need to rethink the calculation and assessment process, and provide suitable automation tools. I consider that vba code has far greater flexibility in developing alternative design tools, than in cell worksheet calculations.
Compare the following examples of AS4600 calculations for phi.Ms against those in the the previously mentioned
calculator application. The calculator has several screens to get to the calculation of phi.Ms, where as in all the following spreadsheet images, the intermediate calculations are hidden, whilst sectional property data is also hidden in an MS Access table. All of the worksheets make use of the same vba function to calculate phi.Ms.
Once schTechLIB is installed and available, making such alternative workbooks is a lot easier than replicating all the intermediate calculations and then meshing with all the additional calculations required for the design of a structural member.
Text book presentation of calculations is the way to understand calculations but it shouldn't dictate presentation for all purposes. Calculations are a means to an end, and a means of reaching decisions, and we shouldn't forget this.
 |
| Simple Calculator to get phi.Ms |
 |
| Simple calculator to get phi.Ms and phi.Mb for each available c-section |
 |
| Lower portion of calculations for design of end wall mullion |
