How to use Excel Solver for Linear Programming: Solving Optimization Problems
basToday we’re going to explain an advanced feature of Microsoft Excel. Here’s a comprehensive guide on Excel Solver Linear Programming to solve optimization problems. Solver will help you solve linear and non-linear problems.
Let’s begin!
Table Of Content
1. What’s Excel Solver?
2. How to add Solver to Excel?
3. How to add Solver to Excel on my Mac?
4. Where’s Solver in Excel?
5. How to use Excel Solver for Optimization Problems?
6. What is Optimization and Linear Programming?
7. Real-Life Example
8. Final Words
1. What’s Excel Solver?
Excel Solver is a tool that you can add to Microsoft Excel. When you need to find the best answer for a problem with certain constraints, this is a powerful tool you can use.
It basically lets you make the most or least of a certain number (usually cost or profit) within a set of variables and limits. A lot of people in areas like finance, operations management, and engineering use Solver to do sensitivity analysis, linear programming, and nonlinear problem solving.
2. How to add Solver to Excel?
Firstly, let’s start with adding Solver to Excel. Solver is an add-in, so you probably do not see Solver tab on your main menu by default. If so please firstly add this useful optimization tool to your Excel workbook.
To add the Solver add-in to Excel:
#Step1: Open the Options dialog box
To get to the options menu, go to File > Options
#Step2: Manage Add-ins
On the Excel Options dialog box, go to Add-ins > Manage Excel Add-ins and click Go.
#Step3: Add the Solver Add-in to your Excel Workbook
Check the Solver Add-in box in the Add-Ins available box, and then hit OK.
It will be installed by Excel and can be found in the “Data” tab, under the “Analysis” group.
Now you have this fantastic optimization tool on your workbook.
3. How to add Solver to Excel on my Mac?
If you’re a Mac user, the installation of Solver may be a little bit different than Windows layout.
To add Solver to Mac Excel:
- Open the Excel app on your MacBook
- In Excel, go to the “Tools” menu.
- Click on “Add-ins for Excel.”
- Check the box next to “Solver” in the list of Add-ins that you can use.
When you click “OK,” Excel will turn on the Solver add-in. Solver is now on the “Data” tab.
4. Where’s Solver in Excel?
Solver is in Excel’s “Data” tab, which is part of the “Analysis” group. Once you’ve added Solver to Excel, click on “Solver” in the “Data” tab to get to it.
Make sure it’s turned on in the Excel Add-ins if you can’t see it.
Now you have set up your Excel Solver and know where it is. Let’s see how to use it!
5. How to use Excel Solver for Optimization Problems in Linear Programming?
So, before diving into a case study, we want to give a general idea about how to use Solver in Excel.
So first let’s meet the Solver concepts.
What are Solver Parameters in Excel?
In Excel, the Solver Parameters are:
- Set Objective: Pick the cell you want to improve.
- To: Pick whether to set the goal cell to a certain value, maximize, or minimize.
- By Changing Variable Cells: These are the cells that the solver will change to reach the goal.
- Subject to the Constraints: These are the rules or restrictions that the answer has to follow.
- Choose a Way to Solve: You can pick Simplex LP for linear problems, GRG Nonlinear for nonlinear problems, or Evolutionary for problems whose answers aren’t clear or are close.
So while building your model, you should at least define your Objective, Variable Cells and Constraints.
Let’s build a very simple model now to see how the Solver works.
#Step1: Define your problem
Be sure that you clarify your objective, variables and constraints. Now let’s start Excel Solver Linear Programming simple case study.
Example Problem: We have two different products, with different units costs and unit prices. We have a total production capacity of 2.000 units. We want to find optimum production levels for each product to reach a net revenue of $6.000.
#Step2: Open your Solver window
Now we’ll first open our Solver window.
#Step3: Enter your objectives and variables
We’ll set objective and variables.
In this example our objective cell is the Net Revenue calculation cell. Then we’ll say that our objective cell should be $6.000.
And lastly, you’ll select the variable cells. In our example we select production levels for each product as variables.
#Step4: Add your constraints
We’ll now add our constraints using the parameters on Add Constraint window.
In our example, our only constraint is the production capacity. Our cell reference should not be more than 2.000 here.
#Step5: Choose your solving method
The Excel Solver gives you three different options to solve your problem.
We’ll not get into mathematical details here but they’re basically used for:
- Generalized Reduced Gradient (GRG): For problems that are smooth nonlinear.
- LP Simplex: For problems that are linear.
- Evolutionary: For problems that are non-smooth.
That’s all!
Now Solver has solved this problem and give us the optimum production levels:
Now you can keep your Solver solution or Restore original values and return to Solver dialog box.
6. What is Optimization and Linear Programming?
A mathematical method called linear programming is used to find the best solution in a model where the links between the parts are linear.
It is the process of finding the best solution for a linear problem that is limited by a set of linear limits. In this case, optimization means either making the goal function bigger or smaller, like making the most money or cutting costs as much as possible.
7. Real-Life Example to solve optimization problem with Excel Solver
Now we’ll give you an real-life example to solve more complex problems. This is a more complex problem than a basic Excel Solver Linear Programming examples.
In this case study, one of our clients requested us to prepare a simulation in Excel in order to test their algorithm before investing in a more expensive software solution.
Let’s come back to our case study.
Case: We have a matching application, which matches people (women to men) according to their hobbies.
Problem: We have 50 women and 50 men in our portfolio and we know their values and hobbies. We want to make optimum matches in order to catch maximum happiness for all users.
Below is the visual presentation of our source data and result matching table. We’ll try to make this table with maximum happiness.
What’s critique on optimization modelling?
It’s easy to find a match for a single user with the maximum common points. But this will not guarantee the maximum happiness for all our user database.
So if we couple the highest matching score people to each other, then there might be left very noncompliant couple prospects left!
That’s to say, if a couple has 9 matches but the other couple has only 3 matches, that’s the ideal solution. We’ll prefer both couples taking at least 7 as a matching score.
Then we have two constraints:
- We should maximize the average matching score across the group
- We should have a minimum matching score of X
So what we have done?
We have first created a matching matrix, in which we can see the matching scores of all possible couples dynamically.
Then we have input a calculation area on the left side with very simple mathematical calculations:
In order not to repeat this task each time, we also add a VBA code to automatize this process:
Now, we’ll try to go over the optimum levels for the two constraints explained above:
In Summary:
This tool basically:
- Puts all users in the matrix
- Calculates the matching scores dynamically.
- Runs Solver to test the optimization for the best solution in the thousands of combinations
- Then finds the most suitable solution for us and writes them to the final table.
Here’s the matching table view:
Our client here had very high-level developer team, and they made simulations based-on our model and tried to find the best algorithm for their application.
Isn’t Excel so impressive?
If you want to boost your Excel skills on functions, you should try our interactive Excel Formulas Trainer tool to practice your knowledge and speed on functions:
– This is the main menu of Someka’s Excel Formulas Trainer Tool with 30 exercises in three levels –
8. Final Words for Excel Solver Linear Programming
As a conclusion, the Solver function in Microsoft Excel is a very flexible and strong device that can handle a lot of different optimization issues.
Being built into Excel makes Solver not only powerful but also easy to use for people who are already familiar with the Excel environment. This article gives a step-by-step guide on how to use Solver’s features, from setting up the problem to figuring out what the results mean.
So hope this Excel Solver Linear programming article with how-to steps and case studies will help you to design optimization models for your linear and nonlinear problems.
Recommended Readings:
Related Posts
