How to Use Solver Add-In in Excel 2016

Edited 2 weeks ago by ExtremeHow Editorial Team

Microsoft OfficeExcelSolver Add-InCalculationSpreadsheetOptimizationWindowsMacFunctionsProductivityTools

Microsoft Excel is a powerful tool for handling data, performing calculations, and making decisions based on numerical information. One of the lesser-known but highly valuable features in Excel 2016 is the Solver add-in. Solver allows you to find the optimal value for a formula in one cell, called the objective cell, subject to constraints on the values of other formula cells. It is particularly useful for what-if analysis and can be applied in areas such as resource allocation, scheduling, budgeting, and decision making.

Understanding the components of a solver

Before learning how to use the Solver Add-in, it's helpful to understand its components:

• Objective cell: This is the cell that contains the formula you want to optimize — either to maximize, minimize, or achieve a specific value.
• Variable cells: These are the cells that the solver will change to achieve the desired result in the objective cell.
• Constraints: These are limits you impose on variable cells, such as setting the maximum or minimum value they can take.

Installing and activating the Solver Add-in

In Excel 2016, Solver is an add-in and needs to be installed and activated before it can be used:

1. Open Excel 2016 and click on the File tab.
2. Select Options, and then choose Add-ins from the menu.
3. In the Manage Add-ins box below, make sure Excel Add-ins is selected and click Go.
4. In the Available Add-ins box, check the Solver Add-in and click OK.
5. The Solver button will now appear in the Analysis group on the Data tab in your Excel ribbon.

Steps to use Solver

After activating the solver, here are the detailed steps to use it:

1. Set up your worksheet
   • Prepare your data in Excel, where one cell contains the formula you want to optimize. This is your objective cell.
   • Identify the decision variables, the cells that you will allow the solver to change to achieve the desired result.
   • Determine the constraints you need to apply to your variables.
2. Access Solver
   • Go to the Data tab on the ribbon.
   • Click Solver in the Analysis group to open the Solver Parameters dialog box.
3. Define solver parameters
   • In the Set Objective box, enter the cell reference for your objective cell.
   • Choose whether you want to maximize, minimize, or find a specific value for your purpose.
   • In Replacing Variable Cells, enter references to variable cells. Use commas to separate non-adjacent references.
4. Add constraints
   • Click Add to enter the constraints.
   • In the Cell Reference box, enter the cell reference for the variable that must obey the restriction.
   • Choose the appropriate operation (=, <=, >=, int, bin, dif) and provide the restriction value.
   • Click OK, and repeat to add more restrictions.
5. Solution to the problem
   • After all constraints have been entered, click Solve in the Solver Parameters window.
   • Solver will attempt to find the optimal solution based on the input data and display the Solver Results dialog. This dialog lets you tell Solver to keep the solution or revert to the original values.
   • Select Keep Solver Solution to apply the changes to your worksheet.

Advanced features of the solver

In addition to the basic steps, Solver provides advanced features that can be useful for more complex analysis:

• Solver Options: Before performing a solution, clicking Options in the Solver Parameters window allows you to configure various advanced settings such as the solution method (e.g., GRG Nonlinear, Simplex LP, Evolutionary) and the precision of the solution.
• Reports: After solving, the solver can generate detailed reports – answers, sensitivities, or limits – that provide information about the results and constraints.
• Solving Non-Linear Models: For non-linear optimization problems, it is important to select the proper solution method and options to obtain accurate results.

Practical example of using the solver

Let's say you're trying to decide how to allocate monthly marketing spend across different channels (SEO, social media, and paid advertising) to maximize engagement. You can use Solver by setting engagement as the objective, with different spends as variable cells, subject to constraints such as budget limits for each channel.

Let's assume that the total amount of SEO, social media and paid ads should not exceed $10000, SEO should be between $2000 and $3500, social media between $1500 and $3000 and paid ads should be at least $3000. The solver helps to optimally distribute the budget for maximum engagement under the given constraints.

Common problems and troubleshooting

Even though the solver is powerful, users may encounter some common problems:

• No feasible solution: This error can occur when no solution satisfies all the constraints. This can be resolved by reconsidering and adjusting the constraints.
• Unbounded solutions: If the solver runs indefinitely, important constraints in your model may disappear, causing the solver to find no end point.
• Returns a solution that is not optimal: This may require you to reevaluate your constraints or change solution methods.

Conclusion

The Solver add-in in Excel 2016 is a powerful tool that extends Excel's analytical capabilities beyond standard calculations. By setting up various scenarios and constraints, Solver gives you the ability to model real-world situations and find optimized solutions, helping to make effective decisions. With good understanding and practice, Solver can transform the way you analyze data and solve complex problems in Excel.

  Share This Article

If you find anything wrong with the article content, you can request an update