As mentioned earlier, Microsoft Excel’s Goal Seek tool allows you to determine the value of a single input cell required to achieve a desired result in a dependent cell (the result cell). Goal Seek can be used for various purposes — for example, it allows you to find the root of an equation directly on a worksheet. However, when solving equations, using Goal Seek requires a relatively large amount of manual work.
Let us outline the basic algorithm of working with Goal Seek in this case:
- Rewrite the equation so that the unknown appears only on the left-hand side, and the right-hand side is a constant. For example, the equation
x2=x+1.
should be rewritten as
x2−x−1=0.
- Go to the File tab on the ribbon and select Options. In the Excel Options window, choose Formulas on the left. On the right, under Calculation options, specify the relative tolerance for finding the root of the equation. For example, 10−5.
- On the worksheet, reserve one cell (e.g., B1) for the unknown. Enter the initial approximation of the root into this cell (for example, 1).
- Reserve another cell (e.g., B2) for the left-hand side of the equation. Enter into this cell the formula of the left-hand side of the equation. In this case:
=B1^2-B1-1
- On the ribbon, go to the Data tab → Data Tools group → What-If Analysis → Goal Seek.
- In the Goal Seek dialog box:
- In the Set cell field, reference the cell containing the left-hand side of the equation (here, B2).
- In the To value field, enter the constant from the right-hand side of the equation (in this case, 0).
- In the By changing cell field, reference the cell containing the unknown (here, B1).
- Click OK.
- The Goal Seek Status dialog box will appear, and the value in B1 will be replaced with the found root. In this example: 1.618037.
Thus, using the standard Goal Seek tool requires quite a bit of manual work. To simplify the process, we can create an application that will automate much of this effort when solving equations.
Building the Application
Create a form with four labels, four input fields, a spinner control, and a button (Figure 9.37). Set the Name property values of the controls as shown in Table 9.14. Enter the given code into the form module and the Sheet1 worksheet module (see also the file 10-Improved Goal Seek.xlsm on the CD).
Table. Name property values set in the Properties window
| Control | Name | Description |
| TextBox | txtBegin | User enters the initial approximation of the root |
| Label | lblBegin | Caption for txtBegin |
| TextBox | txtEquation | User enters the left-hand side of the equation. The equation must be rewritten so that its right-hand side equals 0. The expression must begin with an « = » sign, follow programming syntax, and use « x » as the unknown. For example: =x^2-x-1. In the program, the entered expression will be converted into a worksheet formula. |
| Label | lblEquation | Caption for txtEquation |
| TextBox | txtRoot | Displays the computed root. This field is read-only. |
| Label | lblRoot | Caption for txtRoot |
| TextBox | txtTol | Relative tolerance for root-finding, linked to the spinner |
| Label | lblTol | Caption for txtTol |
| Spinner | spnTol | Sets the relative tolerance for root-finding, displayed in txtTol |
| Button | cmdOK | Reads the initial guess, the left-hand side of the equation, and the tolerance. Assigns cell B1 of the active worksheet the name « x ». Verifies that the entered expression can be converted into a worksheet formula. If not, a message is displayed and the program is stopped. If the input is valid, the initial guess is written to cell B1 (named « x »), and the formula of the left-hand side is written into cell B2. Using the GoalSeek method of the Range object, the root is found. If unsuccessful, a message is displayed. If successful, the root is formatted according to the specified tolerance and displayed in txtRoot. |
