Cable tray calculation

With commercial solar system design, invariably cable tray is used to house DC and AC cable. Trying to calculate what size tray to use, how many lengths, how much lid, the frequency of support is time consuming. That’s why this blog looks at a cable tray calculator in spreadsheet form.

So here goes!

‍

The Spreadsheet, inputs

As can be seen in the image below, I have created a 'Cable tray Inputs' table that allows the user to enter information concerning the brand, model, length, width, the length of run, a buffer for mistakes, lid length, overlap amount and other details.

In most cases there is a numerical figure entered or a Y or N to denote a Yes or No response.

In the case of "Cable tray width in mm" a drop down list is used.

‍

Now, the second 'Cable tray Inputs' table, below right, has additional questions, and the answers determine the number of boxes or packs that need to be purchased. I have used 50 and 10 as my answers but you can put in whatever figure is relevant.

‍

‍

The Spreadsheet, outputs

Now the all important outputs per the table below. We will start with the "No. of 600mm trays lengths". This is a simple formula that first multiplies the 'Length of run...' input (200 m) by 1000 to convert to mm, then divides by the length of tray (3000 mm). Next, it factors in the buffer amount of 2% and finally rounds up.

The "No. of 600mm lid lengths" calculation is similar, but incorporates the overlap input, which in this case is 30mm, and also allows for lid lengths that are different from cable tray lengths.

"No. hex heads screws to attach lid" references our spacing input of 1000mm and considers the length of run multiplied by 2 (both sides of the lid) a rounding up function is used here

‍

‍

The "No. splices" result of 134 relates to the joins between the sections of cable tray so references the number of lengths of tray minus 1, then multiplies by 2. 

This is a similar calculation we do when we look at the number of mid clamps required with panel calculations. The "No. splice bolts" references the 134 amount and as you need two bolts per splice the figure ends up being 268. This is also the case when looking at splice washers, if applicable, and splice nuts.

The “No. hold downs” relates to a yes no question concerning the use of Unistrut. If the answer is Y it looks at the length of the run, the distance between supports (1200 mm), adds 1 then multiplies by 2.

‍

‍

Still looking at our Cable tray Outputs table above, "No. channel nuts/springs", "No. bolts for channel nut" and "No. washers for channel nut" all reference the same basic formula and inputs as "No. hold downs". The blue arrows directly reference the inputs taken to calculate these 4 items.

And you can see the basic formula used below.

‍

‍

Outputs, Continued

Our second 'Cable tray outputs' table, below, looks at how many boxes or packs need to be purchased.

The calculations are simple and rely on the inputs concerning the number of components that a typical box or pack holds divided into the total number of components used. So long as we input accurate box quantities in our second Cable tray Inputs table earlier, we should now have all the information we need to compile a Balance of Materials list.

With a normal commercial solar system, we tend to use more than one cable tray profile so we have to be creative when using this particular spreadsheet. For example, say we are using 150, 300 and 450 mm tray for our DC run on the roof.

My suggestion would be to create another sheet that has three separate outputs and then put in the 150 mm tray, copy the outputs but just paste the values and repeat for all three cable tray sizes.

‍

Conclusion

The ability to calculate quickly what is required from a materials perspective is a time saver that directly helps with a business's bottom line. With enough data more detailed relationships emerge such as the $/watt cost for cable tray on larger commercial solar systems.