Unlike simultaneous linear equations, simultaneous non-linear equations cannot be solved using linear algebra. However, we can extend the concept of using Goal Seek from solving a single implicit equation to solving systems of nonlinear equations.
In our worksheet, we’ll set up equations for flow in an open channel and use them to find the depth of the flow given a flow rate, slope, roughness, and channel width.
This channel has a known flow rate (Q), width (B), and slope (S). The area of flow is dependent on the depth, y, which is what will be solved for. The whole system is governed by these three equations for flow rate (Q), area (A) and hydraulic radius (R):
Both area and hydraulic radius are dependent on y, and both of those terms are in the flow rate equation.
We can solve this system of simultaneous non-linear equations using Goal Seek.
To start, enter a guess value for y of 2 meters. Name that cell y using the name box. The other input cells have been assigned names.
Next, we’ll enter the three equations into Excel. For area:
Name that cell A. For the hydraulic radius, enter the formula:
Excel won’t accept R as a name, so name this cell Rad as an abbreviation for radius.
Now you can calculate the flow rate based on the guess value for y. Since y is just a guess, the value you calculate won’t be equal to the known flow rate, but you can use Goal Seek to adjust the value of y until the calculated flow rate matches the known flow rate. In the cell for flow rate, enter:
Name this cell Qcalc. Obviously, the calculated flow rate isn’t the same as the actual flow rate of 110 m3/s, so you’ll need a better guess for the depth, y. In order to use Goal Seek, set up a cell to calculate the difference between the actual and the calculated flow rate in cell C14:
This cell calculates the error of your guess. You can use Goal Seek to get the error to zero. Select the cell containing the error, then go to Data > What-If Analysis > Goal Seek. It will automatically use the selected cell as the Set cell. Enter zero as the To value. The variable that will be changed to get the error to zero is y, so click that cell.
Click OK and Goal Seek will adjust the value of y so that the difference between the actual flow rate and the calculate flow rate is zero. The final value of y is 1.499 m.
Are you struggling to the find the right solutions to your engineering problems in Excel?
In Engineering with Excel, you'll learn Excel for advanced engineering calculations through a step-by-step system that helps engineers solve difficult problems quickly and accurately.