Excel provides a capability called goal seeking which enables you to find roots of a polynomial equation and a variety of other problems which are typically solved using iterative methods. Essentially you want to find a value of x such that f(x) = a for a constant a.
Excel’s goal seeking capability can be found by going to the Data tab and pressing the small down arrow to the right of the What-If Analysis option from among the Data Tools and then selecting Goal Seek. A small dialog box appears and you need to enter values for the following fields:
- Set cell
- To value
- By changing cell
Set cell should point to the cell that contains a formula for f(x). To value should contain the value a. By changing cell should point to a cell which has the initial guess of the value of x. When you press OK, Excel tries to find a value of x such that f(x) ≈ a. If the iteration converges then Excel will return the solution x in the cell where you put your initial guess.
For problems where there are multiple values of x such that f(x) = a, different initial guesses for x can yield different solutions. Thus to find all the solutions will require repeated trials based on different guesses.
Note that in Excel 2016, there is no What-If Analysis option; instead you use the Forecast option, and so the selection sequence is Data > Forecast|Goal Seek instead of Data > Data Tools|What-If Analysis > Goal Seek.
We can use Goal Seek, for example, to find the sample size required to achieve a specified power for various tests (see, for example, Power of a Sample or Power for t Distribution). We now show how to use Goal Seek to find the eigenvalues of a matrix.
Example 1: An eigenvalue of a matrix A is a constant c with the property that det(A–cI) = 0. Find the eigenvalues of the following matrix A.
Figure 1 – Matrix A for Example 1
We are seeking values c such that det(A–cI) = 0. To accomplish this goal we create the Excel worksheet described in Figure 2.
We have placed array A in range A4:D7 and the identity matrix with the same shape as A in range F4:I7. We now make an initial guess for the eigenvalue of 10 (cell B11). Since Excel will automatically change this value we also put the 10 in the cell A11 so that we can recall the value of our initial guess. Next we put the array formula for A–cI in the worksheet. In the above example we highlight the array F11:I14 and type =A4:D7-B11*F4:I7 and then press Ctrl-Shft-Enter. Finally we put the value det(A–cI) in the worksheet by entering =MDETERM(F11:I14) in cell D11.
We next access the Goal Seek dialog box as described above and enter the following values:
- Set cell: D11 – i.e. the formula for det(A–cI)
- To value: 0 – we seek a solution to the equation det(A–cI) = 0
- By changing cell: B11 – i.e. we want to vary the value of c (our initial guess)
Upon pressing OK, Excel will iterate and find the solution c = 5.058047 as in Figure 3.
Excel automatically replaces the initial guess of 10 by the solution 5.058047 and the initial value -6029.33 of det(A–cI) by a value close to our goal of zero (i.e. -0.00024). Pressing OK on the Goal Seek dialog box locks in this solution.
We can now repeat the process by making different guesses in order to find the other 3 eigenvalues. E.g. if you make an initial guess of 20 and use Goal Seek, you will get the eigenvalue 22.95897. A guess of 0 finds the eigenvalue 0 and a guess of 1 finds the eigenvalue 0.590132.
If we had made an initial guess of 30 instead of 1, then Goal Seek would have found the solution 22.95897 again, and so you might need to make a number of guesses before finding all the eigenvalues.
Observation: The Real Statistics Resource Pack contains an Eigenvalue and Eigenvector data analysis tool which automatically calculates the eigenvalues of a square matrix (and the corresponding eigenvectors). For more information, see Eigenvalues and Eigenvectors.
Observation: Goal Seek seeks an answer within 100 iterations of its algorithm or when the accuracy of the answer is better than .001 (whichever occurs first). These default values can be overridden by selecting File > Options > Formulas (or Office Button > Options > Formulas in Excel 2007) and changing the values for Maximum Iterations and/or Maximum Change.
Excel provides another iteration tool, which is more powerful than Goal Seek, called Solver. Whereas Goal Seek enables you to find a solution to the equation f(x) ≈ a, Solver enables you to solve equations in multiple unknowns, such as f(x1,x2,…xn) ≈ a. It also allows you to find the values of x1, x2, … xn which maximize or minimize the value of f(x1,x2,…xn) subject to constraints.
Solver can be used to solve problems similar to those addressed by Newton’s Method. In particular, it can be used to solve a variety of regression problems. See Finding Logistic Regression Coefficients using Solver for one such example and Example 2 below for another.
To access Solver select Data > Analysis|Solver. If you don’t see Solver as a choice you will need to load it into Excel. You can do this as follows:
- In Excel 2007, click on the Microsoft Office button (see the upper left-hand corner of Figure 1 in Excel User Interface) and then select Excel Options. In Excel 2010/2013 select File > Help|Options.
- Click Add-Ins and then in the Manage box select Excel Add-ins
- Select Go
- In the Add-Ins available box, click the Solver Add-in checkbox and then press OK.
Once you access Solver you will be presented with a dialog box which contains the following fields:
- Set Objective: enter a cell address (or click on the cell). This contains the formula for f(x1, x2, … xn) as described above
- To: select one of the three radio buttons: Max, Min, Value Of (and then set the value)
- By Changing Variable Cells: here is where you specify the cells containing x1, x2, … xn.
These fields are similar in function to the three fields in Goal Seek as described above, except that you also have a maximize and minimize capability for the second field and most importantly you can change multiple cells in the third field. In addition Solver provides a capability for setting constraints. This allows you to specify constraints such as x2 ≥ 0.
Solver also lets you choose one of three solving methods. For our purposes we will always choose GRG Nonlinear (the default).
Example 2: Find the regression coefficients for Example 1 of Least Squares for Multiple Regression using Solver.
The left side of Figure 4 contains the data from Example 1 of Least Squares for Multiple Regression. Cells F4, F5 and F6 contain the regression coefficients, each initially set to a guess of 1. Column H contains the predicted values of the Price based on the regression coefficients (e.g. cell H4 contains the formula =A4*F5+B4*F6+F4) and column J contains the error terms (e.g. cell J4 contains the formula =C4-H4).
Finally cell F8 contains the value of SSE, which is calculated using the formula =SUMSQ(J4:J14).
We now select Data > Analysis|Solver and fill in the fields in the dialog box that appears as specified in Figure 5.
Here you specify that you want to minimize the value in cell F8 (which contains SSE) by changing the values in the range F4:F6, i.e. the regression coefficients. Once you press the Solve button the dialog box in Figure 6 appears, which announces that a solution has been found.
You can now click on OK and the cells in the worksheet in Figure 4 change to reflect the solution, as described in Figure 7.
The result is the same as we obtained in Figure 3 of Least Squares for Multiple Regression.
Observation: if you press the Option button on the Solver dialog box (see Figure 5), you refine the accuracy of the solutions arrived at by Solver. E.g. you can change the values of Constraint Precision default 0.000001), Maximum Time (Seconds) and Iterations.