# What Is Excel Solver?

## Solve your programming woes with this popular Excel add-in

The Excel Solver add-in performs mathematical optimization. This is typically used to fit complex models to data or find iterative solutions to problems. For example, you might want to fit a curve through some data points, using an equation. Solver can find the constants in the equation that give the best fit to the data. Another application is where it is difficult to rearrange a model to make the required output the subject of an equation.

## Where Is Solver in Excel?

The Solver add-in is included with Excel but it isn’t always loaded as part of a default installation. To check if it’s loaded, select the DATA tab and look for the Solver icon in the Analysis section.

If you can’t find Solver under the DATA tab then you will need to load the add-in:

1. Select the FILE tab and then select Options.

2. In the Options dialogue box select Add-Ins from the tabs on the left-hand side.

3. At the bottom of the window, select Excel Add-ins from the Manage dropdown and select Go…

4. Check the check-box next to Solver Add-in and select OK

5. The Solver command should now appear on the DATA tab. You’re ready to use Solver.

## Using Solver in Excel

Let’s start with a simple example to understand what the Solver does. Imagine that we want to know what radius will give a circle with an area of 50 square units. We know the equation for the area of a circle (A=pi r2). We could, of course, rearrange this equation to give the radius required for a given area, but for the sake of example let’s pretend we don’t know how to do that.

Create a spreadsheet with the radius in B1 and calculate the area in B2 using the equation =pi()*B1^2.

We could manually adjust the value in B1 until B2 shows a value that is close enough to 50. Depending on how accurate we need to be, this might be a practical approach. However, if we need to be very exact, it will take a long time to make the required adjustments. Actually, this is essentially what Solver does. It makes adjustments to values in certain cells, and checks the value in a target cell:

1. Select DATA tab and Solver, to load the Solver Parameters dialogue box

2. Set Objective cell to be the Area, B2. This is the value that will be checked, adjusting other cells until this one reaches the correct value.

3. Select the button for Value of: and set a value of 50. This is the value that B2 should achieve.

4. In the box titled By Changing Variable Cells: enter the cell containing the radius, B1.

5. Leave the other options as they are by default and select Solve. The optimization is carried out, the value of B1 is adjusted until B2 is 50 and the Solver Results dialogue is displayed.

6. Select OK to keep the solution.

This simple example showed how the solver works. In this case, we could have more easily got the solution in other ways. Next we will look at some examples where Solver gives solutions that would be difficult to find any other way.

## Fitting a Complex Model Using the Excel Solver Add-In

Excel has a built-in function to perform linear regression, fitting a straight line through a set of data. Many common non-linear functions can be linearized meaning that linear regression can be used to fit functions such as exponentials. For more complex functions the Solver can be used to perform a ‘least squares minimization’. In this example, we will consider fitting an equation of the form ax^b+cx^d to the data shown below.

This involves the following steps:

1. Arrange the dataset with the x values in column A and the y-values in column B.

2. Create the 4 coefficient values (a, b, c, and d) somewhere on the spreadsheet, these can be given arbitrary starting values.

3. Create a column of fitted Y values, using an equation of form ax^b+cx^d which references the coefficients created in step 2 and the x values in column A. Note that in order to copy the formula down the column, the references to the coefficients must be absolute while the references to x values must be relative.

4. Although not essential, you can get a visual indication of how good a fit the equation is by plotting both y columns against the x values on a single XY scatter chart. It makes sense to use markers for the original data points, since these are discrete values with noise, and to use a line for the fitted equation.

5. Next, we need a way of quantifying the difference between the data and our fitted equation. The standard way to do this is to calculate the sum of the squared differences. In a third column, for each row, the original data value for Y is subtracted from the fitted equation value, and the result is squared. So, in D2, the value is given by =(C2-B2)^2. The sum of all these squared values is then calculated. Since the values are squared they can only be positive.

6. You are now ready to perform the optimization using Solver. There are four coefficients that need to be adjusted (a, b, c and d). You also have a single objective value to minimize, the sum of the squared differences. Launch the solver, as above, and set the solver parameters to reference these values, as shown below.

7. Uncheck the option to Make Unconstrained Variables Non-Negative, this would force all coefficients to take positive values.

8. Select Solve and review the results. The chart will update giving a good indication of the goodness of fit. If the solver doesn’t produce a good fit on the first attempt you could try running it again. If the fit has improved, try resolving from the current values. Otherwise, you could try manually improving the fit before resolving.

9. Once a good fit has been obtained you can exit the solver.

## Solving a Model Iteratively

Sometimes there is a relatively simple equation which gives an output in terms of some input. However, when we try to invert the problem it is not possible to find a simple solution. For example, the power consumed by a vehicle is approximately given by P = av + bv^3 where v is the velocity, a is a coefficient for the rolling resistance and b is a coefficient for aerodynamic drag. Although this is quite a simple equation, it is not easy to rearrange to give an equation of the velocity the vehicle will reach for a given power input. We can, however, use Solver to iteratively find this velocity. For example, find the velocity attained with a power input of 740 W.

1. Set up a simple spreadsheet with the velocity, the coefficients a and b, and the power calculated from them.

2. Launch the Solver and enter the power, B5, as the objective. Set an objective value of 740 and select the velocity, B2, as the variable cells to change. Select solve to start the solution.

3. The solver adjusts the value of the velocity until the power is very close to 740, providing the velocity we require.

4. Solving models in this way can often be faster and less error-prone than inverting complex models.

Understanding the different options available in the solver can be quite difficult. If you’re having difficulty obtaining a sensible solution then it’s often useful to apply boundary conditions to the changeable cells. These are limiting values beyond which they should not be adjusted. For example, in the previous example, the velocity should not be less than zero and it would also be possible to set an upper bound. This would be a speed you’re pretty sure the vehicle cannot go faster than. If you are able to set bounds for the changeable variable cells, then it also makes other more advanced options work better, such as multistart. This will run a number of different solutions, starting at different initial values for variables.

Choosing the Solving Method can also be difficult. Simplex LP is only suitable for linear models, if the problem isn’t linear it will fail with a message that this condition was not met. The other two methods are both suited to non-linear methods. GRG Nonlinear is the fastest but it’s solution can be highly dependent on the initial starting conditions. It does have the flexibility that it doesn’t require variables to have bounds set. The Evolutionary solver is often the most reliable but it requires all variables to have both upper and lower bounds, which may be difficult to work out in advance.

The Excel Solver add-in is a very powerful tool which can be applied to many practical problems. To fully access the power of Excel, try combining Solver with Excel macros.

Was this page helpful?