When setting up the solver parameters, which of these cells must include a formula?

Excel Solver is a tool, also referred to as Data Analysis add-in, used to find the optimal solution in the decision-making process mostly for business and engineering related problems. For example, we can find the optimal advertising campaign with regard to budget to maximize profitability. We will learn how to use Solver to find the optimal solution by adding Solver to Excel.

How to Add Solver to Excel

Excel Solver add-in is available in all the Excel versions but it is not included by default. Therefore we have to add it manually in our Excel version by going through the following steps.

• In Excel 2007, go to the Microsoft Office button and then click on Excel Options. But in Excel 2010 and later versions, go to File and then click Options.
• On the left sidebar of the Excel Options, select Add-in > Manage box and select Excel Add-in and press Go button.

• From the available Add-ins dialog, select Solver Add-in and click OK button. After getting installed the Solver Add-in is available in the Analysis section of Data tab.

How to Use Solver in Excel

We need to set up the data in the worksheet before using the Solver function to find the optimal solution. Suppose we need to run an advertising campaign for a defined budget of $45000 for a defined time frame of 10 weeks. We have planned to run 100 adds per week and we need to calculate the minimum cost per add using Microsoft.Solver.

As we have set up the data as per our problem description, now we need to run the Solver by clicking Data tab > Solver in Analysis section. The Solver parameters window will open having the following sections;

Set Objective

We first need to set the object by choosing the objective cell C6 which calculates the payment terms having the formula =C3/(C4*C5) in terms of the number of weeks to run an advertisement for. Then we need to choose the optimal objective of maximum, minimum or a set value to achieve. We’ll use 10 weeks in our example.

Variable Cells

In this section, we need to select the variable cells in our problem model having data to change to meet our set objective We have cells C4 and C5 contain the variable data of adds per week and cost per add. If these cells are non-adjacent then manually enter the cells references separated by commas.

Setting Up Constraints

Solver constraints are limitations or conditions applicable to the problem’s solution. In the Subject to Constraints section of the parameters window, click on Add button to add the constraints.

In the Add Constraints dialog, enter the constraints and press Add button. When we are done with adding all the constraints then we press the OK button. Like, we have added the constraints for the total budget amount and total adds per week.

After setting up the parameters, we need to click on the Solve button.

When Solver solves the problem a Solver Results window appears where we need to select Keep Solver Solution option and press OK button.

Instant Connection to an Expert through our Excelchat Service

Most of the time, the problem you will need to solve will be more complex than a simple application of a formula or function. If you want to save hours of research and frustration, try our live Excelchat service! Our Excel Experts are available 24/7 to answer any Excel question you may have. We guarantee a connection within 30 seconds and a customized solution within 20 minutes.