Cable tray calculator revisited

Friday, October 22, 2021
Veli Markovic

Commercial solar DC cable runs in tray

One of the big differences between domestic and commercial solar system design is the use of cable tray and in this presentation we will look at the use of cable tray on the roof of a large distribution warehouse that houses a commercial  solar array.

The tray in this example is used to accommodate the over 100 DC cable runs.

We take a spreadsheet approach that allows the designer to assign a particular name to a particular cable tray run, calculate how much tray, tray lid and associated materials required for each individual run and the overall amount.

The inputs

We will be using Excel but any spreadsheet will work so let’s get started!

We will configure the spreadsheet into a series of columns the first being Row number, second will be Roof description, third,  length of tray and fourth the width of tray used.

Let’s continue on. The next series of columns includes:

  • Run length in metres
  • Buffer %
  • Lid length
  • Lid overlap in mm

Run length, lid length and Lid overlap in mm are self explanatory. The Buffer % is to allow for mistakes and unknowns, e.g. 4% adds 4% to the run length.

Basically the inputs for each cable tray row can be dragged down. 

Next inputs include:

  • Lid screw spacing
  • Supports under tray, unistrut etc
  • Distance between supports
  • Length of pieces being used as supports
  • The lengths it comes in 

The inputs continued

With the ‘what pieces are you using’ , we see our first formula that references the selected tray’s width and adds a certain amount, in this case, 80 mm to accommodate the cable tray hold downs.

The outputs

Now the outputs replicate the first 2 x columns from the inputs but the third column here uses a formula to calculate:

  • How many tray lengths are needed
  • No. of lid lengths
  • Hex screws and 
  • Splices

The outputs explained

Tray lengths references the cable tray run and divides by the length of tray used and then rounds up.

This also happens with the lid BUT the length is reduced by the amount of overlap.

Hex screws references the spacing input and the cable tray run and adds two and the splices are the same as panel mid clamps, number of trays minus 1 x 2.

The outputs continued

The next three columns use the number of splices figure:

  • No.  splice bolts
  • No.  splice washers, if app.
  • No.  splice nuts
  • For every 1 x splice there is 2 of the above

The next three columns use the number of supports figure:

  • No. of feet used
  • No.  hold downs
  • No.  channel nuts/springs
  • No.  bolts for channel nut
  • No.  washers for channel nut

These all relate to the number of structural supports under the tray

The outputs nearly there

Next on the list is:

  • Number of pieces from length
  • Length left over from piece
  • No. lengths needed
  • Roundup

Length left over can be used to gauge waste and also if minimum waste is => than minimum length required for a smaller width  tray it can be used.

The Nitty Gritty

So we have the outputs for all the rows and now we need to know the total material list for this project cable tray wise. 

As there are a few different tray profiles we need to know the totals.

The formulas to ‘gather up’ all the info are as follows:

  • Tray width list uses the Unique function and looks at I4:I8 and identifies the trays used
  • No. of lengths uses SUMIF function, looks at same range, then at the Tray width and no. of lengths
  • No. of lid lengths does the same BUT looks at no. of lengths
  • No. of support lengths, again the same as the above

Nitty Gritty continued

This section outputs the smaller bits and pieces ranging from the number of splices all the way through to no. of washers for channel nuts.

Note: these calculations are independent of the width of the tray as this is irrelevant.

We can go one better

What if we quickly wanted to know what tray was being used on what row and the totals for those rows.

For example,  a 450mm tray. For this we use data validation, the drop down list, filter and the sum function.

What about a 300mm tray?

600mm tray?


On big commercial solar projects the ability to accurately calculate material costs is a decided advantage and cable tray is no exception. In this presentation we looked at how to approach this problem from a spreadsheet perspective and the same logic can be applied to other aspects of your next commercial solar project..

If you’d like to see what Greenwood Solutions get up to in the real world of renewable energy, solar, battery storage and grid protection check out our industry and commercial pages:

About the author

Veli Markovic

CEC Designer
Veli has nearly two decades of experience in the renewable industry. He is passionate about providing people with valuable education and is highly regarded throughout the industry as an educator and operator.
Training videos

Every week we publish new videos and walkthroughs online.

More Posts

Related posts for you

Greenwoood nominated for CEC solar awards
October 26, 2023

Greenwoood nominated for CEC solar awards

Commercial solar for a fish farm
September 6, 2022

Commercial solar for a fish farm

Trentham solar & battery: revisited
August 9, 2022

Trentham solar & battery: revisited

Ground Mount and Agrivoltaics
July 26, 2022

Ground Mount and Agrivoltaics

Solar and batteries; life in the Mountains
July 20, 2022

Solar and batteries; life in the Mountains

MC4 connections: how to crimp correctly
June 1, 2022

MC4 connections: how to crimp correctly

Looking for a partner to deliver your sustainable vision?