Constrained Optimization in Excel – Maximize Open Channel Flow

In this post, you’ll learn how to perform constrained optimization in Excel through an example where we will maximize the flow rate in an open channel. The example will show that there is an optimal relationship between the channel dimensions that maximizes the flow rate for any required cross-sectional area.

If you aren’t interested in the problem setup, click here to go directly to the constrained optimization setup.

Video

Subscribe to EngineerExcel on YouTube

Constrained Optimization in Excel – Maximize Flow in an Open Channel

This example will demonstrate constrained optimization in Excel by maximizing the flow rate in an open channel with a trapezoidal cross-section.

The geometry of the open channel is defined by three variables:

  • T, the top width
  • h, the height
  • θ, the angle of the side walls
open channel flow dimensions

Without any constraint on the cross-sectional area, the flow could be increased indefinitely by increasing any of the geometry variables.

However, if we place a constraint on the cross-sectional area, we will be able to find the optimum relationship between the three variables that provides for maximum flow

Flow in an open channel is maximized when the hydraulic radius of the geometry is greatest. So instead of maximizing the flow rate, which depends on other variables such as the slope of the channel of the channel, we can optimize the flow rate by maximizing the hydraulic radius. Hydraulic radius is defined as the cross-sectional area divided by the wetted perimeter:

hydraulic radius equation

Inputs

There are four inputs for this calculation:

  • Top Width
  • Height of the channel (depth of the water)
  • Angle of the walls
  • Target cross-sectional area

Placeholders are added for the time being and will be used as variables in the optimization we will set up in a later step. As always, units are added for clarity.

excel optimization inputs

 

Outputs

There are four outputs which will be calculated:

  • Bottom width of the trapezoidal channel (optional, but it makes subsequent calculations easier)
  • Cross-sectional area
  • Wetted perimeter
  • Hydraulic radius

Constrained Optimization Steps

Step 1: Calculate the width at the bottom of the channel

The bottom width of the trapezoidal channel is calculated as a function of the top width, height, and side wall angle using the following equation:

open channel bottom width equation

The formula in Excel looks like this:

open channel bottom width excel formula

 

Remember that all trigonometric functions in Excel require the angle arguments to be in radians, so we use the RADIANS function to convert the angle from degrees before evaluating its tangent.

Step 2: Calculate the cross-sectional area in Excel

The cross-sectional area calculation for a trapezoid (where “b” is the bottom width) is straightforward:

trapezoid cross-sectional area equation

 

And here is the formula in Excel:

trapezoidal cross-section area in excel

 

Step 3: Calculate the wetted perimeter

The calculation for wetted perimeter is probably the most difficult one in this spreadsheet:

wetted perimeter trapezoidal open channel equation

This is the formula in Excel:

wetted perimeter for trapezoidal open channel excel formula

Step 4: Calculate the hydraulic radius

The hydraulic radius is the final output to be calculated in the spreadsheet. It is simple the area divided by the wetted perimeter, and we end up with a value of about 0.48 meters.

hydraulic radius formula excel

 

Step 5: Open Solver and set the objective

We can use the Solver add-in to run this constrained optimization in Excel. The Solver add-in is opened through a button on the far-right side of the Data tab. (If you don’t see it, that probably means you need to enable the Solver add-in.)

Once open, we need to tell Solver which cell result we want to optimize. This is known as the Objective Cell. In our example, we will be maximizing the hydraulic radius, which is the results in cell G6.

excel solver objective

The default behavior in Solver is to maximize the result.

Step 6: Set the Solver variables

Once the objective has been defined, Solver will also need information on which cells to modify to achieve the objective. In the diagram at the beginning of this post, we identified those variables as the top width, the height, and the wall angle.

Click in the field “By Changing Variable Cells:” and select cells C3:C5.

excel solver variables

Step 7: Set up the constrained optimization in Excel Solver

Without a constraint on this problem, Solver would target an infinite hydraulic radius by increasing the top width and height to infinity (for any wall angle).

To prevent this behavior, a constraint is added which will force Solver to stay within some limits. This is referred to as constrained optimization.

A constraint can be placed on an objective cell, variable cell, or any cell in the worksheet.

To constrain this optimization problem, first click on the “Add” button on the right side of the Solver window to open the “Add Constraint” window:

add constraints excel solver

 

After the new window opens, the constrained cell is set as follows:

  1. Click in the “Cell Reference” field
  2. Select the cell value to be constrained
  3. Click in the “Constraint” field
  4. Choose the cell containing the constraint value

In our case, the “cell reference” is the value of the cross-sectional area and the “constraint” is the value of the target area. The constraint could also be a numerical value, but it’s a best practice to choose a cell.

Finally, set the constraint behavior in the middle field. Either “≤” or “=” are valid selections in this case.

constrained optimization excel

 

Once all the fields are set, click “OK” and the constraint will be added to Solver.

Results

After clicking the “Solve” button in Solver, the constrained optimization will be completed in just a few seconds and we can examine the results.

The first thing to notice is that the constraint has been obeyed because the output cross-sectional area is equal to the target area.

constrained optimization results

 

Additionally, Solver has optimized the values of the top width, height, and wall angle to some values.

excel solver optimization results

 

The thing that stands out most about this is that the optimal wall angle is 60 degrees, which is the same as the angle between the sides of a hexagon. This seems reasonable, because we would expect the wetted perimeter to decrease (and hydraulic radius to increase) as the geometry of the open channel becomes more like a semicircle. Also, the ratio between the top width and height is equal to 2.3. Let’s see if these relationships hold up for a different cross-sectional area target.

Change the target area to 5 m^2 and rerun the optimization in Solver to get the following results:

optimization constraints

 

Once again, the constraint has been obeyed, the wall angle is 60 degrees, and the ratio of the top width to height is 2.3. It looks like we’ve found an optimal geometry relationship for flow through an open channel!

Related Topics

References

Scroll to Top
Complete... 50%
Please enter your name and email address below to receive a link to the ebook.

You’ll also receive regular tips to help you master Excel for engineering.

FREE EBOOK:

10 SMARTER WAYS TO USE EXCEL FOR ENGINEERING

By Charlie Young, P.E.

Take your engineering to the next level with advanced Excel skills.