Goal Seeking and Solver

Goal Seek

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.

image3064

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.

Goal seek dialog box

Figure 2 – Data for Example 1 plus Goal Seek dialog box

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.

Goal seek eigenvalues

Figure 3 – Output from Goal Seek

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.

Solver

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).

Regression coefficients Solver

Figure 4 – Initial configuration for Example 2

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.

Solver dialog box Excel

Figure 5 – Excel’s Solver dialog box

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.

Solver solution confirmation

Figure 6 – Confirmation of a solution from Solver

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.

Solver regression coefficients Excel

Figure 7 – Finding regression coefficients using Solver

The result is the same as we obtained in Figure 3 of Least Squares for Multiple Regression.

9 Responses to Goal Seeking and Solver

  1. Alessandro says:

    Hi,
    no problem with method 1 (goal)
    but i haven’t undersood how determine the eigenvalues x1,x2,x3 at the same tim with solver:
    it’s ok that you can point to ie 3 cells in the variable cells of solver…
    but have you to create 3 lambda*I matrix and 3 determinant of (M-lambdaI) for calculating this?

    • Charles says:

      Hi Alessandro,
      As stated in the Example, this method only finds one of the eigenvalues. To find the other eigenvalues you need to use different initial guesses.
      If you want to find all the eigenvalues at once you need to use other techniques. This is described on the webpage
      Eigenvalues and Eigenvectors
      Charles

  2. Mohammad Ghoreishi says:

    let me know how can i find a vector such k’ that it is equal to l’x.
    for example x is a 4*6 matrix as follow
    1 1 0 0 1 0
    1 0 1 0 0 1
    1 0 1 0 1 0
    1 1 0 0 0 1

    • Charles says:

      Is I’ an identity matrix? If so then I’x is a 4×6 matrix and so can’t be a vector. Please clarify your questions.
      Charles

  3. Tony says:

    Sorry it’s OK – I managed to resolve my problem. Thank you.

  4. Tony says:

    Hi Charles, would you be able to provide a fuller explanation of how to obtain all four eigenvalues of the matrix in your example at once using Solver? I have not been able to do this.

    Regards
    Tony

    • Victor says:

      I also would be interested in knowing this. My thought is you could populate a random number after executing the solve, and have it continue until it produces n unique values to correspond to an n x n matrix (say, in VBA), but my guess would be there is a more direct approach to the answer, Tony. Perhaps Charles would be so kind as to reveal some of the secret sauce behind the eValues function? 😉

      Victor

  5. Aj says:

    Hello Sir,

    This is one of the easiest method of finding the co-efficients and intercepts that I have come across. Thank you for this but can you please let me know if this is as effective and accurate as finding the co-efficients and intercepts using about statistical tools such as Minitab, R or SAS

    • Charles says:

      Aj,

      Goal Seek and Solver tend to be sensitive to the starting value, but so far in my experience these tools have worked very well. You can tune the accuracy in Excel.

      For finding regression coefficients the normal least squares method is easy and completely deterministic. For logistic regression, in addition to using Solver, I have also implemented a solution for finding the coefficients/intercepts using Newton’s Method. In my experience it gives excellent results.

      Charles

Leave a Reply

Your email address will not be published. Required fields are marked *