Catenary Cable Tension Calculator in Excel

In this post, you’ll learn how to create a catenary cable tension calculator in Excel by solving an implicit equation. The example will show specifically how to use Goal Seek for finding roots of equations in Excel.

Video

Watch on YouTube | Subscribe to EngineerExcel on YouTube

Catenary Cable Tension Calculator in Excel – Finding the Root of an Implicit Equation

Catenary Cable Background

A catenary cable is a cable hung between two points that are separated horizontally by some distance. The only load acting on the cable is its own weight:

catenary cable tension

The image below shows a free body diagram of a section of the cable. At the lowest point of the cable, the tension force is horizontal.

catenary cable free body diagram

And the tension at the lowest point is related to the geometry of the cable by this equation:

catenary cable tension equation

where

  • T is the horizontal component of tension
  • W is the weight of the cable per unit length
  • y0 is the height of the lowest point of the cable measured from some reference
  • x and y are points that the cable passes through measured from that same reference.

Solving for Catenary Cable Tension in Excel

If the geometry and mass properties of the cable are known, this equation could be solved for the tension.

However, that’s difficult because T is an implicit variable, and the equation cannot be rearranged to put T alone on one side of the equal sign.

Instead, root finding can be used to calculate the tension. In root finding the equation is rearranged so zero is alone on one side of the equation.

Then an iterative method is used to find the value of T that causes the right side of the equation to be equal to zero.

catenary cable root finding

How to Calculate Catenary Cable Tension in Excel

The quickest way to find roots in Excel is to use Goal Seek. The steps to find the roots of the above equation, and thereby the tension at the low point in the cable are below. The spreadsheet has already been set up to store the inputs and the outputs.

catenary cable tension in excel

Step 1: Enter the Input Values for the Catenary Cable Calculation

There are four inputs to the problem:

  1. The weight per unit length, w, has a value of 20 N/m
  2. The height of the lowest point in the cable, y_0, has a value of 6 meters
  3. The known vertical position, y, is 18 meters
  4. The known horizontal position, x, is 45 meters.
catenary cable calculation inputs

 

Step 2: Enter a Guess Value for the Cable Tension

Tension is technically an output but is entered as a value in the spreadsheet. In a subsequent step, Goal Seek will be used to automatically iterate through values of T to find the correct value.

Call this value T and enter an initial guess value of 1500 Newtons.

calculation outputs

Step 3: Create Names for the Variables in the Catenary Cable Equation (Optional)

Excel equations are much easier to read and understand when names are assigned to cells that will be included in the formula. This step is completely optional but can be a huge benefit.

To create names for the catenary cable inputs, select all the cells containing the variable names and values. (It’s ok if some extra blank cells are also selected.)

select cells

Next, navigate to the Formulas tab and select “Create from Selection“.

excel create from selection ribbon

In the window that opens, make sure that the left column box is selected. Once it is selected, click okay.

excel create from selection

Now each of the cells has been assigned a name corresponding to the variable name entered in the cell to the left. Clicking each cell reveals the name in the Name Box in the upper left of the Excel window.

named cells excel

Step 4: Enter the Formula for the Catenary Cable

With names applied to the cells, it is much easier to type the formula for the cable by calling the cells by name.

The formula includes the COSH function, which returns the hyperbolic cosine of an argument in Excel.

catenary cable tension formula excel

Because the Tension value entered in Cell D9 is just a guess, the result of the formula is not zero. In other words, it is not a root of the equation.

excel formula result

Step 5: Open the Goal Seek Tool

Goal Seek can start from the initial guess value and iterate to find a value of T that will make the result of the formula equal to zero.

To open the Goal Seek tool:

goal seek in data tab

Step 6: Set up Goal Seek to Find the Tension in the Cable

Once Goal Seek has opened, set it up to iterate on the value of tension until the equation is equal to 0.

  • Change the “Set Cell” to cell D10 (or the cell containing the formula).
  • Enter a value of “0” in the “To value:” field.
  • Finally, select D9 (or the cell containing the value of cable tension) for the field “By changing cell:”
goal seek setup

Step 7: Run Goal Seek to Calculate the Catenary Cable Tension in Excel

Once all those fields have been filled out, click OK. Goal Seek should find a solution within seconds, often in less than a second.

Results

In this example, Goal Seek achieves a value of 1.08×10^-5, which is essentially zero plus a negligible amount of error. The resulting tension is 1726 Newtons.

Of course, keep in mind that if any of the values in the “Inputs” section change, Goal Seek must be run again. However, it is possible to automate Goal Seek so that it runs whenever a change occurs on the spreadsheet, as explained in this post.

root finding result excel

Related Topics

References

Scroll to Top