Traditional (UK) Scaffolding

[Wanabe]

For 20+ years, I've used a spreadsheet to calculate prices for contract hire scaffolding based upon three variable costs, labour, equipment hire and transport.

I've been toying with the idea of extending the functionality of the sheet to produce an approximate load list of required components.

'Tube & clip' scaffolding is composed of horizontal components, some parallel to and others perpendicular to the face of the working area. There are also two sets of diagonal components in two planes, again one parallel to and the other perpendicular to the face of the working area. Finally, there are vertical (supportive) components.

To calculate the number and size of horizontal and diagonal components is purely mathematical but my first problem is how to produce, for example, the total number of 5ft long tubes when this size could relate, depending on the dimensions of the scaffold, to some or none of the components required.

The second problem relates only to the number and size of vertical components. These are calculated partly mathematically, partly in accordance with industry practice and also in accordance with legal requirements. I can produce a lookup table to list all of the permutations, but I still have the problem of how to collate the resultant data.

Ultimately, I'm trying to produce one list of components for one scaffold in sizes ranging from 1ft to 21ft (and in 1ft increments in between).

There are essentially only seven discreet components, 'inside standards' (a line of vertical tubes closest to the working area), 'outside standards' (a line of vertical tubes further away from the working area), 'ledger braces' (diagonal tubes in a plane perpendicular to the working area), 'facade braces' (diagonal tubes in a plane parallel to the working area), 'transoms' (short horizontal components placed perpendicular to the working area), 'ledgers' (pairs of parallel horizontal tubes in the same horizontal plane running parallel to the working area) and 'handrails' (horizontal tubes similar to ledgers but positioned to in order to protect persons accessing the scaffold from falling of).

I can calculate load lists manually with ease. It's my ambition now to produce them automatically.

I think it's a big ask, but I was wondering whether there is any one (or more!) on this site who is up for helping me out?

[shg]

It seems like a natural application for Excel; the obstacle is that you know the synthesis aspects, and we don't.

Maybe you could sketch and post the various designs and their parameters.

[oldchippy]

Hi shg,

I know he's talking UK scaffolding, but it basically like the diagrams here

http://apps.leg.wa.gov/WAC/default.aspx?cite=296-24-862

[Wanabe]

Will do. Sketches and parameters to follow. Thanks.

[Wanabe]

Because of a strange tradition, all dims are metric except for scaffold tube dims.

Input cells are (figures for illustrative purposes):-

B7 Elevation Length (m)? 16.2m
B8 Top Scaffold Height (m)? 8.4m
B9 Scaffold Width (m)? 1.5m (min 1.2m max 1.8m)
B10 Max. Bay Size (m)? 2.1m (min 1.2m max 2.7m) Actual bay size 16.2m/2.1m = 7.71 = 8 in reality = 2.025m per bay.
B11 Lift Height? (m)? 2.1m (min 1.5m max 2.4m) Actual lift height 8.4m/2.1m = 4 therefore actual lift height is 2.1m.
B12 Number Levels Boarded? 2 (only working levels to be boarded and assuming )

Output cells are (i.e. C20 would give the total number of 19' tubes required):-

B C
18 21'
19 20'
20 19'
21 18'
22 17'
23 16'
24 15'
25 14'
26 13'
27 12'
28 11'
29 10'
30 9'
31 8'
32 7'
33 6'
34 5'
35 4'
36 3'
37 2'
38 1'

The parameters for the different components are, as follows:-

Ledgers (longitudinal tubes, running in pairs, in a horizontal plane, for the full (building) elevation length)

So, for an elevation length of 16.2m. there are 2 x (21' + 21' + 12') ledgers

Handrails (horizontal longitudinal tubes, in vertical pairs for boarded levels and singly for non-boarded levels)

So, for the elevation length of 16.2m and 8.4/2.1 = 4 number of levels with 2 levels boarded, there would be 2 x 2 (21' + 21' + 12') handrails to boarded levels but only 2 x (21' + 21' + 12') handrails to unboarded levels. At scaffold ends, short tubes, again in singles or pairs to be positioned to protect those ends with a tube size the same as for transoms...see below. In this case 2 (ends) x 2 (working levels) x 2 x 5' plus 2 (ends) x 2 (non-working levels) x 1 x 5'... i.e. 12 x 5' end handrails.

Facade Bracing (diagonal tubes in a vertical plane parallel to the working area...size of tube assumed to be 10' long for all cases...such braces to be positioned min every six bays and to the full height of the scaffold) In this case, bays 1 and 7 would have zigzag full height bracing = 2 (bays) x 4 (lifts) x 10' tubes.

Ledger Bracing (diagonal tubes in a vertical plane perpendicular to the working area...size of tube assumed to be 8' for all cases...such braces to be posioned every alternate bay (but always on the end of all scaffolds) and to the full height of the scaffold) In this case, bays 1, 3, 5, 7, 9, would have zigzag full height bracing = 5 (bays) x 4 (lifts) x 8' tubes.

Transoms (short horizontal board supports positioned at 4 per 3.9m run of scaffold, to every level, but never less than 1.0m apart and never less than 2 under a short board. The length of tube is determined by the width of the scaffold) In this case, total lenght of 16.2m/3.9m = 4.154. This prescribes 4 (lifts) x ((4 x 4) + 2) x 5' transoms...that is 72 x 5' transomes.

Standards (vertical tubes in two parallel planes with 'inside standards' (a line of vertical tubes closest to the working area) and 'outside standards' (a line of vertical tubes further away from the working area) forming the main support for the scaffold. Inside standards (except those at the end) need to reach the top lift only (8.4m in this case). Outside standards (plus any end inside standards) need to reach the top handrail which is 1m above the top lift (9.4m in this case). The problem here is that tube length does not follow a mathematical model.

I am struggling placing this next bit into words, so I'll upload a table later.

I will also upload diagrams which will hopefully makle all this more understandable.

[Wanabe]

I've attached a sketch to identify scaffold components as they are used in the UK.

Table to follow to demonstrate choice of size of vertical tubes (standards) as a scaffold becomes higher.

[Wanabe]

A lookup table is still under construction, but here is a basic narrative.

The max length of scaffold tube is 21' (6.4m).

As scaffolds grow in height, it becomes necessary to butt joint additional tubes ('toppers') to create the desired finished height.

Joints in standards must not be present in adjacent lifts both laterally and longitudinally.

Inside standards (except those at the end of a run of scaffold) only need to be as high as the top level of scaffold. Outside standards (including end inside standards) need to be 1m (about 3') higher to reach the handrail which protects the top level of scaffold.

Scaffolds up to 18' high will not need 'toppers' because the inside standards only need to be 18' high and the outside standards 21' high.

Just above 18' high, the outside standards will require 'topping out'. So, in the case of a working lift at 20' high, for example, the inside standards will only have to be 20' high, but the outside would need to be 23' high to accommodate the handrail.

Because adjacent joints cannot be in the same lift, a row of outside standards to a 20' high scaffold could be 16' + 7' then 21' + 2' then 16' + 7' then 21' + 2' and so on. For this example of a 20' high scaffold, this results in adjacent vertical joints to outside standards below lift 3 then above lift 3 then below lift three and so on.

Just when you might think that you are grasping the gist of this, in reality 'standard' sizes of tubes tend to be used for standards. These are typically 5', 8', 10', 13', 16', 21'.

So, in the 20' high scaffold example, in reality the outside standard sequence would be 16' + 8', then 21' + 5', then 16' + 8', then 21' + 5' and so on.

For much higher scaffolds where both inside and outside standards need topping out, the sequence for both inside and outside standards becomes, respectively, 21' + 21' + x', then 16' + 21' + y', then 21' + 21' + x' with the other plane of standards being 16' + 21' + a', then 21' + 21' + b', then 16' + 21' + a'. This is done to keep the vertical jointing of standards out of sync between the inside and outside rows.

As soon as I can produce a suitable table, I will append it to this thread.

[Wanabe]

Here are the details for typical choice of inside and outside standard sizes.

[Wanabe]

I would be delighted to spend as much time as you wish with additional explanation. For the non-scaffolder it must look like a nightmare task...but, if I can manually work out load lists with my tiny mind, there must be a wizz or two on here who can produce the same results mathematically!

All help most welcome.

[Wanabe]

I apologise if I have over-faced you!

[Wanabe]

Concluded no need for lookup table to decide 'standard' size choice. Initially going to allow size to be chosen mathematically and gather all results for tubes calculated to be of length 'x' using the IF cell = x function.

[Wanabe]

Sheet one complete. Now trying to link multiple sheets to cover elevations 2 through 8 to give an overall price and load list.

[Wanabe]

OK. Sheet just needs testing.

Next question.

I want to give the user the opportunity to calculate scaffold prices/load lists for multiple elevations of buildings. I'm thinking of using one sheet per elevation then collecting a summary on a front sheet.

How do I create multiple sheets and what formulae are used to add up the contents of cells on these other sheets?

[Wanabe]

OK. Extra sheets created using Insert>Worksheet.

And, summing cells in 3D achieved using =SUM(Sheet2:Last!C18)

So, next question!

Before data is input into Sheet 1, the cells at C18 on sheets 2 through 7 display #DIV/0!. Until sufficient data is input on Sheet 1 to enable a numeric value to be resolved for ALL cells C18, the SUM(Sheet2:Last!C18) formula will not return any form of answer.

Can anyone explain how I can work round this?

[shg]

Change the formulas in C18 on those sheet so they don't return #DIV/0!

[Wanabe]

Thanks for that, shg.

Attempted to remove all #DIV/0! errors using the IF function e.g. =IF(C61=0,"0",(B56-1)) but I'm getting a circular reference error.

I've launched the Circular Reference toolbar in View>Toolbars>Circular Reference but it's not obvious how to proceed.

[shg]

What formula is currently in the C18 cells that give the errors?

[Wanabe]

C18 on sheets 2 to 7 inclusive contains the formula =(B61*C61)+(B63*C63)+(B65*C65)+(B67*C67), where,

B61 contains =TRUNC(B8/6.4) and displays 0 until data is input on sheet 1
C61 contains =(B56-1) and displays #DIV/0! until data is input on sheet 1

B63 contains =TRUNC((B8+1)/6.4) and displays 0 until data is input on sheet 1
C63 contains =(B56+3) and displays #DIV/0! until data is input on sheet 1

B65 contains =(B58*2)*(INT((B7+C7)/6.4)) and displays #DIV/0! until data is input on sheet 1
C65 contains =INT((B7+C7)/6.4) and displays 0 until data is input on sheet 1

B67 contains =(((B58-B12)*1)+(B12*2))*(TRUNC((B7+C72)/6.4)) and displays #DIV/0! until data is input on sheet 1
C67 contains =INT((B7+C7)/6.4) and displays 0 until data is input on sheet 1

[shg]

Work you way back to the beginning of the problem and fix it there.

C16 can't result in #DIV/0! unless one of it's arguments is.

Neither can C61 or C63.

[Wanabe]

C61 contains =(B56-1) and displays #DIV/0! until data is input on sheet 1

Looking at sheet 1 and working back from the above formula...

B56 contains =B7/B55 and displays #DIV/0! until data is input...

B7 contains nothing and is awaiting data input
B55 contains =B7/TRUNC((B7/B10-0.001)+1) and displays #DIV/0!

B7 contains nothing and is awaiting data input
B10 contains nothing and is awaiting data input

The 'closest' formula to blank cells is =B7/TRUNC((B7/B10-0.001)+1) but when I attempt to use the IF cell = 0 function to remove the #DIV/0!, namely =IF(B55=0,0,B7/TRUNC((B7/B10-0.001)+1)), I receive the circular reference error.

I suspect it might have something to do with the high prevalence of B7 in formulae, but this value is critical being the length of the scaffold structure along the building facade and is pivotal to the many of the equipment calculations both in terms of tube size and tube numbers.

[oldchippy]

Hi,

Try this

B55 =IF(OR(B7="",B10=""),"",B7/TRUNC((B7/B10-0.001)+1))

B56 =IF(B7="","",B7/B55)

C61 =IF(B56="","",B56-1)

[Wanabe]

Thanks for the suggestion, but replacing each of my formulae with yours across all six sheets still results in #DIV/0!'s

[oldchippy]

Hi,

May be the best thing to do is to download your workbook?

[Wanabe]

Thanks oldchippy, but I've managed to overcome the #DIV/0!'s on the worksheet at least before any data is input. During input, they come and go as various cells resolve a numerical value but it doesn't impede the functioning of the sheet overall.

I used the IF function on problematic cells by referring them all to C7 (length of scaffold) which displayed as "0" on sheets 2 through 5 until data is input on sheet 1. Very similar thing to what a number of people have suggested, so thanks for that.

Another question, though, if I may.

The formula =INT(MOD(C8,6.4)/0.3048) calculates the length of vertical tube required (in feet) necessary to reach the top level of scaffold from the last 6.4m (21') tube that will fit below. So, if the top level of scaffolding is at 24', the formula assumes that this will be made up of a 21' tube (the longest available) with a 3' tube placed above.

Two problems with this:-

1. Even if this formula worked properly for all cases (and it doesn't...please see 2 below), it produces a wholly mathematical result which doesn't match what actually happens in practice. In reality, the choice of size of tube for this particular purpose would be from a range, 5', 8', 10', 13', 16'. So, in the above case, a 5' tube rather than a 3' tube would be installed by the scaffolder.

However, to complicate matters further...

2. As soon as a scaffold reaches 6.4m (21') high, a different layout of vertical tubes needs to be utilised. This is to prevent the incidence of juxtaposition of vertical joints in tubes which is not permitted in UK law. Above 6.4m (21') in height, adjacent lower vertical tubes would alternate between 16' and 21' with (again, in the cited case), upper tubes (in reality) alternating between 8' and 5' (mathematically between 8' and 3' if you see what I mean).

A few other considerations.

The above formula calculates the tube necessary to reach the top working level of scaffolding for the inside line of vertical tubes. The outside line of vertical tubes needs to be about 1.1m higher than the top level of scaffold to provide a fixing point for the safety handrails.

In the case of a higher scaffold where the lower vertical tubes alternate between 16' and 21' tubes, additional tubes placed on top of those 16' or 21' tubes will always be 21' in length until such time that additional 21's become unnecessary due to the finite height of the scaffold.

To enable my worksheet to produce a 'working' load list, I need to address all of the above points. I've considered LOOKUP tables but I'm finding this next step a little daunting.

I suspect that this part is more to do with mathematics than Excel, but I would really appreciate any help you can offer.

[Wanabe]

Two problems solved.

To change mathematically calculated results to standard equipment sizes:-

=LOOKUP(D58,{0,5.5,8.5,10.5,13.5,16.5},{5,8,10,13,16,21})

To add up all instances of a particular 'looked up' value:-

=SUMPRODUCT($F$52:$F$73*($E$52:$E$73=B18))


Getting so close, I can smell it!

[Wanabe]

Thanks for your help so far. I've appended a link to a small video file of the prototype in use (this site doesn't support the upload of .wmv files).

http://www.sendspace.com/file/e2k0x8

I'd like to add 'print buttons' to print out a) the full sheet and b) load list only.

Never used macros before, so please be gentle.

Help would be appreciated.

[Wanabe]

Solved the print button problem with help from others.

For those who need it, I share below!

With the sheet launched in Excel, press ALT + F11 to open the Visual Basic Editor and select Module from the Insert button and paste in...

Sub PrintAll()
'Print used range
With ActiveSheet
.PageSetup.PrintArea = .UsedRange.Address
.PrintOut
End With
End Sub

Sub PrintSome()
'Print defined range
With ActiveSheet
.PageSetup.PrintArea = "A1:E25"
.PrintOut
End With
End Sub

...then press ALT + Q to close the code window. Insert an image onto your spreadsheet, right click the image, select Assign macro and assign PrintAll or PrintSome. Repeat for the other image.

[shg]

Please take a few minutes to read the forum rules, and then edit your post to add CODE tags.

Thanks.

[Wanabe]

Looking for help with VB.

Want to place an image in a work sheet which, when clicked, will toggle on and off protection for the range L7:L14 to give users the ability to edit default settings. Ideally, the colour of the text in those default cells will be green when unprotected and red when protected.

[Richard Buttrey]

Hi,

The following should do what you want. Just assign the macro to your image object.
Note: It assumes the image is on the same sheet as the L7:L14 range in question. If not you'll need to modify the Range to include the sheet name.

Sub ToggleLockedCells()
 
    ActiveSheet.Unprotect

    With Range("L7:L14")
        If .Locked = True Then
            .Locked = False
            .Interior.ColorIndex = 4
        Else
            .Locked = True
            .Interior.ColorIndex = 3
        End If

    End With
    ActiveSheet.Protect
End Sub

Rgds

[Wanabe]

It's the text colour that I want to control, so I deleted Interior and inserted Font.

Works a treat.

Thank you.

[Wanabe]

How can I restrict the use of a worksheet to one computer?

I believe that macros can be used to ID the serial number of a PC's hard drive and that an algorithm can be used to generate an activation code which would prevent migration.

If it is possible (by any method), I would appreciate advice on how to achieve this.

[shg]

Please start a new thread with an appropriate title for your new question.

[Khairulann]

does the scaffold calculator formula can be used for Scaffold made from G.I. Pipe

[FDibbins]

Khairulann, welcome to the forum

1st, this thread is over 3 years old, I doubt the members above are still following it
2nd Unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.

[Neilkh]

Hi Wanabe.... just found this thread.... how did you get on with the load list table?.... is it possible to get a copy please?

[FDibbins]

Hi Wanabe.... just found this thread.... how did you get on with the load list table?.... is it possible to get a copy please?

This thread is over 10 years old, I doubt anyone is still following it

[nancywelc]

If anyone have this spreadsheet please can u post link?

Thank u

[AliGW]

Administrative Note:

Welcome to the forum.

We are happy to help, however whilst you feel your request is similar to this thread, experience has shown that things soon get confusing when answers refer to particular cells/ranges/sheets which are unique to your post and not relevant to the original. This thread is over 10 years old, so it is not appropriate to ask here.

Please see Forum Rule #4 about hijacking and start a new thread for your query.

If you are not familiar with how to start a new thread see the FAQ: How to start a new thread

There is no 2022 version of Excel - please update your profile to clarify which version you have.