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