Problematic week. Something simple became complicated and more time consuming than it should have been. Poses an anomaly or paradox with use of software especially spreadsheets.

In the first instance my adoption of spreadsheets was to:

  1. Improve presentation over hand written calculations
  2. Reduce round-off errors on carry through to dependent calculations
  3. Reduce transcription errors between hand written calculations and numbers punched through pocket calculator
  4. Save time hand writing repetitive calculations sequences
  5. Rapidly gain insight into limitations of materials and structural forms
  6. Achieve consistency of approach
  7. Build on existing calculations and increase detail of assessment

When creating spreadsheets the time taken either equals the time taken to produce hand written calculations or takes considerably longer. The greater length of time is usually a result of increasing the detail of calculation, complications with presentation, or adding conditional tests and error trapping. However, the typical result is that some 40 hours worth of hand written calculations can be collapsed to 1 hour of working through a spreadsheet. Or at least that is the case if using the spreadsheet, and tweaking its capabilities on a regular basis.

Now my first spreadsheets, using QPro,  for structural design didn’t deal with analysis except for the simplest of elements, rather the spreadsheets focused on design-actions, member design, connection design and footing design. The spreadsheets wrote data files for our plane frame analysis software, and was relatively easy to modify gable into skillion, and change from enclosed shed to open canopy. But having to use structural analysis software is a major bottleneck, so better if can do the analysis in the spreadsheet as well. So for sheds I used Kleinlogel formula for gable frames. For gable canopies using RHS I modelled as simple beams. For doubly pitched (gable) canopies made from timber I worked out the formulae for the typical collar-tied frame and built workbook to design entire structure.

The full spreadsheets can save a considerable amount of time, if and only if the proposed structure is fully compliant with the structure modelled in the spreadsheet. Now whilst inputting parameters into a spreadsheet may take only 5 minutes,  skimm reading the results can take an hour or more. But if it’s over a month since I last used the spreadsheet I may need 5 hours or more to work through the detail of the workbook.

Now the advocates of MathCAD and the likes, would put it down to lack of diagrams, and formula hidden in cells. And I  have kind of fell into this mentality, and added diagrams, presented formula, and added more descriptive wording, but it doesn’t help. Its like at university, or school, the lecturer works through a proof on the blackboard, and then declares: it obviously follows the result is … But it isn’t obvious, neither students nor graduates are working through such things year after year, the same as the lecturer, no the students/graduates work through such proofs once or twice. The student may require an additional 10 steps to grasp what is happening. But putting those extra 10 steps in, doesn’t help, it’s still not a matter of glancing at the text and leaping to a conclusion of obvious. Once understand the derivation, then may be, just may be, can leap the 10 steps and no longer require.

Similar situation exists for the spreadsheets. When I use the spreadsheet on a regular basis, I know what the spreadsheet is doing, I just skim the pages and review critical results. A few months later and I have to read the pages, and work through them in detail. The issue doesn’t have anything to do with what is hidden in vba, or worksheet cells, the issue is the relevance of the sequence of calculations to the current project, and what needs to be customised and what can be reused. When used on a regular basis I can more rapidly add extra calculations and delete those that are irrelevant: and customise to the task at hand.

Put another way my calculator spreadsheets for steel, cold-formed steel, and timber are faster to use than those for shed or canopy design. The reason is that the shed design spreadsheet for example, is designing a specific set of beam-columns with specific conditions to AS4600. Where as the cold-formed steel design spreadsheet can be used to design/check any c-section or built-up variation. The shed spreadsheet has fewer input parameters because more design decisions have been made relative to the structure being considered.

Or consider another situation, I regularly use the formula M=wL^2/8, but I don’t have to, I can just start at one end of the beam and take sections and calculate purely with numbers. Some engineers do take the latter approach, but it isn’t necessarily readable or otherwise easy to follow. Others use the formula M=WL/8 where W=wL. Familiarity is important part of proficiency: it can otherwise be related to learning curves or experience curves.

Its something which manufacturers of structural building products fail to pay attention to. Not having technical people on staff may reduce costs, but in 10 years time, when standards change, who is going to be familiar with their product or have any interest in such product? In 10 years time someone is going to have to take an interest and then get up to speed. Aluminium balustrades for example are a prime example: aluminium is not a common structural material, it is an expensive code as are associated industry design manuals, it is a complex code, it doesn’t follow the approach of other structural codes, and barriers are rarely designed because they can be bought. so where are they going to find a designer.

Any case the question is: Is it faster to start with a blank worksheet and do all the calculations from scratch, or to use an existing spreadsheet? I believe using an existing spreadsheet is faster, it achieves consistency where it is needed and saves time. The problem is getting the time allowance for the job and pricing more reliable. The 1 hour task, may occasionally take 20 hours, and then take several more projects before get back down to 1 hour. The infrequency may mean that it always takes 20 hours.

This Weeks Problems

This weeks problems weren’t so much to do with spreadsheets but with structural design and consistency between projects and otherwise explaining differences.

Project Consistency

Last week and this I was looking at two attached verandahs. One was L-shaped and located in wind class N2 area, the other was simple rectangle located in wind class N1. At first glance at the drawings the spans of each rectangle looked similar. So with a reduction in wind load, consistency was suggesting lower bending moments, not similar and not larger. The design pressure for N2 is qzu=0.96kPa and that for N1 is qzu=0.69kPa, thus wind class N1 only has 72% of the load. However closer look indicated that the span increased by approximately 1.13, since moment is proportional to square of the span that is a (1.13)^2=1.27 magnification of moment. Whilst the load width increased by 1.28, thus total moment increase 1.27×1.28=1.63 if they were using the same wind class. Taking the wind class into consideration then nett increase in moment is 1.63×0.72=1.17.  And thus the differences in moments calculated by my simple canopy design spreadsheet were explained, and found to be the correct order of magnitude. But that wasn’t what I looked at first, rather I looked at live loading and any magnification of load due to reduction in area: and none was found. So I needed to look closer.

Frame Analysis

I use a stepped approach to structural design. I start with simple beams, move onto plane frames(2D) and then 3D analysis. In the main I try to avoid 3D analysis has it takes too long to build the models. I mainly use 3D analysis to gain insight into behaviour so that can develop and/or confirm simple analysis models for isolated members. For attached canopies however, I use 3D analysis to check magnitude of forces likely to be imposed on the house the verandah or carport is being attached to: but only when the canopy is attached near hips: which seems to be most of them.

So I start with beams checked using a spreadsheet. Then I create a 2D model in Multiframe, a structural analysis program. I typically start with either a freestanding model, or just the rafters. If I start with the rafters, I adopt pinned supports to both sides, then turn one side into a roller support. The roller support should model the flexibility of the post, and give similar moments in the rafters to the simple beam model. If use a freestanding model, then the rafters are attached to the posts by pinned joints, the posts are cantilevered from the ground. The members are checked then, the column on one side is removed, and replaced by a roller support, and then by a pinned support. This support is on the house side of the canopy. A pinned support is typically adopted in the final design. If the members work, then move onto 3D analysis.

Its is with the 3D analysis that I struck into problems. Multiframe permits the use of either pinned joints or member end releases. It is usually simpler and faster to use pinned joints than release members around a joint. If release too many members around a joint then structure can be become unstable, releasing the appropriate number of members can be time consuming exercise. Unfortunately arraying the 2D frame into a 3D structure seldom works with the pinned joints, and it becomes better to release members at the joints. So I built the 3D model, and ran the analysis, and got huge moments in the post bases. Larger than into the 2D models, and larger than in any prior project.

I knew what was causing the moments, and so I introduced tie-beams, which whilst they are part of the specified structure I leave out until needed. I never remove them from the final specification, they make the structure more stable and robust, otherwise reduce the deflections. More importantly the ridge connection is only nominally a moment connection, leaving the tie-beams in means it doesn’t have to be a moment connection. My task is to assess that a proposed structure is satisfactory, and specify that which is to be added to make it so.

Problem is why did I need the tie-beams, don’t usually need  them? I have AS4600 and AS4100 modules for Multiframe,  these can quickly check members, and the members were ok. However I summarise the moments, and check the members using my AS4600 calculator spreadsheet. I was about to write the summary when I noticed that the moment distribution in the rafters wasn’t as expected.

The source of the problem, was the end release for the rafters at the ridge. In the process of removing pinned joints and replacing with releases, I kind of got carried away and pinned the ridge. With the tie-beam present the ridge can be pinned and the rafters have smaller moments. The specified ridge connection however is nominally rigid, so I check initially that don’t need the tie-beams. Such approach means that a quick back off envelope calculation, assuming rafter as a single span beam, can confirm that the structure is in the “ball-park”, and such maybe all the independent check that is required.

Purlin Design

The cold-formed shed and carport industry has adopted C75’s for girts and purlins, this choice limits spacing of the main frames to 3m, it also limits spacing of the girts/purlins to around 900 mm, but more typically down around 700 mm. Roof cladding, like Lysaght custom orb ( common corrugated) cladding has span limits of 700mm for single span, 900mm for end spans and 1200 mm for internal spans. Small structures typically only have two cladding end spans, and no typical spans so cladding limited to 900mm. However edges of structures (eaves and ridges) experience turbulence and thus higher surface pressures than other regions of a roof surface. This typically reduces cladding spans to less than the default values: the default values largely dependent on liveloading requirements and the de-indexing of the sheets if someone stands on the cladding. Most other cladding profiles can span further than 1200 mm, and the common solutions for the high pressure zones is simply to halve the span of the cladding or use lapped z-sections and increase the material thickness. the point is the shed/carport manufacturers think the cladding is the limiting factor, when in reality the C75 is the limitation. The C75’s make since when used as girts on residential structures, where such girts occupy limited floor space. On the other hand larger girts can be placed between the larger columns, and be used as shelves, that don’t lose storage space. Anycase it’s been awhile since looked at the capabilities of C75’s as most manufacturers have changed to tophats, or box sections for carports. Furthermore its something which look at once, and then simply accept: that is whilst frames change their size from project to project to purlins remain unchanged.

So I have multiple spreadsheets for purlin design, showing less or more detail for the calculations. Most of the spreadsheets simply determine the design action-effects, only one carries out design and it makes use of load capacity tables. Recently I have been using one of the detail spreadsheets, and then checking the purlin using my AS4600 calculator spreadsheet. Spans have typically been less than 3m and the checks have indicated the purlins are adequate if and only if the occasional 1.1 kN point load is distributed to two or more purlins. This week I got back to the 3m limit, and hit the problem how did we make this thing work in the first place. (Though most of shed design specifications typically required 1 to 2 rows of bridging. The C75’s which builders use are not clearly defined, and not all are capable of the same spans. Some are based on questionable testing involving a pile of bricks: such results are also proprietary and not available for general use. Calculations thus give different answers than such tests.)

So first problem, was that quick check is to halve the moment from the 1.1kN load, and compare against capacity. Its a quick check but not strictly correct for the load case 1.2DL+1.5PL, where only the PL gets halved. However I have a spreadsheet so can quickly halve PL, and check the results. Bad result. I used multispan beam coefficients and did a quick check for double span case: and it helped. Except that 1.2DL+1.5PL ceased to be critical and 1.2DL+1.5LL became critical, which reduced the value of “cb” used to magnify the value resistance (phi.Ms). I then created a purlin model with single, double and triple spans, and various pattern loadings in Multiframe. Including consideration of a multispan plank to check reactions at the purlins. A single span plank between two purlins reduces the 1.1kN load to 0.55kN, but this isn’t the case for double and triple span cases: the second support gets higher than half the load, whilst the other supports get significantly less. With multispan beams the effect of an action at one end, dwindles out as move toward the far end.

So multispan purlin not providing enough assistance, and multispan work plank may make things worse than a single span plank.  So purlins work at 3m, I know they supposed to work at 3m, so what have I forgotten. Pins, inflexion points, lateral restraint, compression flange, R factors (never used, only valid for cleats, not flange to flange), bridging, “cb” values, assumptions. That’s it the assumption : cladding provides full lateral restraint, no need to mess with “cb” and phi.Mb and segment length, can compare against phi.Ms.

I check the older spreadsheets and the check for 1.1kN load was added as an afterthought: no 1.2DL+1.5PL, just straight out PL, compared against a load capacity w[kN/m] calculated from phi.Ms. The critical concern for all these shed designs was wind loading versus area live loading (LL). The usual problem was reducing spacing to get the purlins to work for wind loading, only to have LL increase because smaller areas have magnified loads, in some situations the C75’s became impractical and had to move to the larger C100’s: because it wasn’t possible to reduce the purlin spacing to reduce the load, it just gets higher and higher. The general idea of the area LL is to ensure a minimum total load of 1.8kN, since it is higher than 1.1kN, the lower load wasn’t considered important.

.For one one-off shed design typically select purlins to achieve the maximum span of the cladding. Only in high wind areas such as Learmonth (WA), does the practicality of the purlin override the maximum span of the cladding: a C300 purlin maybe suitable, but it kind of looks silly and unstable: thus reduce span of cladding and size of purlins.

As for 1.1kN without a partial load factor, that is something I was going to discuss in detail with respect to other issues mentioned in prior posts. But a quick run down is that: 1.1kN is near enough the 95th percentile weight of a person, so to multiply it by 1.5 is unreasonable for limit state design, even though doing is expected to give the same results as the previous permissible stress codes. Consider that 1.5×1.1 = 1.65kN , which is the equivalent of a person weighing 168 kg: is such person going to be working at height on a roof.  Australian bureau of statistics data suggests that 97% of the Australian population has a weight less than 110kg. Whilst a report in the UK, HSE (2005),RESEARCH REPORT 342, “Revision of body size criteria in standards Protecting people who work at height”, indicates that the 95th percentile weight of the UK population is 100kg, and asks is the population of people working at height different. Based on this report my assessment is that weight of a person at 1.1kN is adequate, whilst a worker with tools is around 1.2kN, and a worker with tools and materials is 1.4kN, and no partial load factors need be applied as these are the extreme values.

Then take into consideration that for small structures people work from ladders or mobile scaffolding, and for large structures they use cherry pickers or elevating platforms: there is potentially no need to check for such point load. However there are future maintenance operations during which people may walk on the roof. Without roof cladding purlins are potentially unstable and should not be stood on. There have been reported cases, where structures have collapsed during construction because lateral bracing assumed in design was not there during construction. Thus the structural member would be structurally adequate in its finished state, but not during the construction process.

Thus can show that the C75 is adequate for the 1.2DL+1.5PL and 1.2DL+1.5LL, on condition that it is laterally restrained with roof cladding. During construction however the cladding isn’t there, so the load reduces to 1.5PL without lateral restraint. If the purlin not adequate for this load case, then can either double the purlins up, back-to-back, along entire span, or just where the moments are maximum. Or otherwise advise against standing on the purlin during construction.

Though my preference would be not to rely on the roof cladding and keep the span of C75’s around 2m to 2.4m, but the industry adopted C75’s on 3m grid a long time back. From my experience, I have revised shed designs which were originally written back in 1985, and I have been revising and originating since at least 1996. Can probably assume that such use dates back further than 1985, and we are kind of stuck with the using of C75’s.

And Onto Other things

Some suppliers have indicated that they don’t like top-hat sections because the tek-screws from the cladding are visible. I’m not sure how valid this is, as main experience with top-hats is with large sheds, with heights of 6m or more to the roof, the screws are not that visible. The screws are also not all that visible on the walls: but I guess it depends on the spacing of the girts. If girts are below eye level and above eye level, then the screws won’t be that noticeable, the top-hat hides the screws from view. As for looking up at the roof structure, c-section framing isn’t that attractive in the first place, so I guess making it less so with top-hats isn’t all that desirable. However most cold-formed steel carport manufacturers have now adopted boxed sections for all elements of the framing: posts, rafters, beams and purlins. Thus as with timber framing the roofing screws are not visible.

The question is how hidden are roofing screws when C75’s are used compared to top-hats? I guess with the usual orientation of the opening of the c-section facing up the slope, the screws are hidden from view. I guess the top-hats could be closed with a sheet metal cover, or a plastic clip on cover. No holes need drilling for top-hats, they can be fastened with self-drilling screws: with right quality screws and power driver its relatively faster than bolting. Top-hats also achieve the main objective of the C75′ they occupy less space: top-hats, and batten sizes range from about 40mm deep to 120mm. Whilst the smaller sections would have problems with a 3m span as designed for the 600mm to 1200mm span requirements of house construction, the intermediate sizes around 64mm maybe be suitable: though on large sheds we have typically opted for 96mm or the 120mm depths.

The cold-formed steel house framing companies now have advanced software, and can design all manner of framed structures using 75mm to 90mm channel sections. Where the cold-formed shed industry may use a C250, these companies may use a 250mm deep fabricated truss. They may also use a tapered truss, so whilst fabrication costs may increase there is some possibility of less material. Now whilst the frames supporting the roof are at 3m centres or greater, between these frames is simple stud wall framing to infill the space. These walls are potentially easier to clad, and line externally and internally and can also be insulated. They are thus easier to adapt into extra living spaces, compared to a typical shed or verandah.

So if want to put in extra labour and design effort then C75’s can be used for a lot more than just girts and purlins. On the other hand the house industry indicates that the C75’s are more versatile when used as part of triangulated framework, than when used on their own. That is whilst the wall and roof frames are made from 75mm deep cee type sections, the roof cladding is supported on top-hats: of course it’s another situation where the screws are not visible.

Top-hats are potentially more stable than a cee, and if a cover holds the legs together then they would be more stable again. So for exposed c-section structures and top-hats how to close the open faces? The box sections the carport industry uses are actually two specially formed c-sections which clip together. Whilst RHS,SHS,CHS are also effectively specially roll-formed c-sections with lips long enough to touch, which can then be seam welded to close. But unless we have biaxial bending, we don’t really need box sections, and webs are potentially excess weight.

So what we need for a canopy, is closed section which keeps birds, and insects out along with leaf litter, but doesn’t add too much extra weight or expense. Thin plywood could be screwed in place, but that’s a lot of extra work, a thin plastic clip on cover however could be factory installed. Since it may obstruct access to the connections however, it is probably better installed on site. Alternatively if its flexible it can be easily pulled out off the way. Yet another alternative is that the cover can be in parts, and removed and replaced in the vicinity of connections.

Though one early idea I had for c-sections was to fill them with polystyrene or similar foamed polymer, besides stopping the accumulation of flammable dusts in some factory environments, the main idea was to improve the lateral stability of the sections by hindering the rotation of the flanges. Similar idea could be applied to top hats, the foamed polymer fills the internal space, and provides more material to screw into, and hides the screws at the same time: though I doubt it would do much for the stability. Its an extra inline manufacturing process, but shouldn’t be too much of a problem as industry already manufacture sandwich roof cladding with either foamed polymer between two metal sheets or just applied to one metal sheet. For structural sections, just need a former to close up the section whilst the foam is applied. However need to select an appropriate foam to avoid creating a fire hazard.

Just a matter of appropriate industrial product design.


  1. [02/02/2018]: Original
  2. [24/02/2018]: Modified some of the expressions as the mathematical operators seemed to disappear.