Solver

Introduction

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(x₁,x₂,…x_n) ≈ a. It also allows you to find the values of x₁, x₂, … x_n which maximize or minimize the value of f(x₁,x₂,…x_n) 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.

Using Solver

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 versions of Excel starting with Excel 2010 select File > Help|Options. 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.
• 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 that contains the following fields:

• Set Objective: enter a cell address (or click on the cell). This contains the formula for f(x₁, x₂, … x_n) 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 x₁, x₂, … x_n.

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 x₂ ≥ 0.

Solver also lets you choose one of three solving methods. For our purposes, we will always choose GRG Nonlinear (the default).

Example

Example 1: Find the regression coefficients for Example 1 of Least Squares for Multiple Regression using Solver.

The left side of Figure 1 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).

Figure 1 – Initial configuration for Example 1

Finally, cell F8 contains the value of SS_E, 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 2.

Figure 2 – Excel’s Solver dialog box

Here you specify that you want to minimize the value in cell F8 (which contains SS_E) 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 3 appears, which announces that a solution has been found.

Figure 3 – Confirmation of a solution from Solver

You can now click on OK and the cells in the worksheet in Figure 1 change to reflect the solution, as described in Figure 4.

Figure 4– Finding regression coefficients using Solver

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

Optional Features

If you press the Options button on the Solver dialog box (see Figure 2), 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.

Sometimes Solver will find a local minimum or maximum value instead of the global minimum or maximum. This can be corrected by choosing different initial values. You can have Solver do this automatically by pressing the Options button on the Solver dialog box and then choosing the Use Multistart option from the GRG Nonlinear tab of the Options dialog box. Solver will now run multiple times using different starting values, picking the values that produce the best outcome. The cost of this option is slower run times.

Reference

Microsoft Support (2019) Define and solve a problem using Solver
https://support.microsoft.com/en-us/office/define-and-solve-a-problem-by-using-solver-5d1a388f-079d-43ac-a7eb-f63e45925040