Are you interested in learning how to solve Linear Programming in Excel? Linear programming has numerous applications in various fields such as allocating resources, scheduling, optimizing vehicle routes and supply chain networks, analyzing game theories, etc.
In this article, we will present how to use Excel for solving linear programming problems using the Solver Add-in. It is a built-in Excel Add-in that can solve linear and non-linear programming problems that contain multiple variables and constraints. For solving linear programming models, it uses the Simplex LP method. While using the Excel Solver Add-in, we can also save and load a linear programming model for reuse purposes.
We can also apply the graphical method to solve a linear programming problem in Excel. However, this method is applicable only for problems with two variables.
We used Excel 365 for our operations while preparing this article. But you can find all the features and functions used in this article in Excel 2010 or newer versions.
Click the image to get a detailed view
Download Practice Workbook
How to Do Linear Programming with Solver in Excel?
Excel Solver Add-in is a built-in tool, which is used for solving linear or non-linear optimization problems in Excel. It is a four-step process: adding Excel Solver Add-in, formulating a linear programming problem, tabulating the problem, and finally, solving the problem using Excel Solver.
How to Add Excel Solver Add-in?
The Excel Solver Add-in is not present in the Data tab by default. In such instances, we have to add in from the Add-ins menu of the File tab.
- To add the Solver add-in from Excel Add-Ins, go to the Data tab >> check if the Solver add-in is present >> go to the File tab if the Solver tool is not present.
- Click on the Options menu.
- Afterward, from the Excel Options user form, click on the Add-ins option >> click on the Go option.
- Now, from the Add-ins user form, check the checkbox of Solver Add-in option >> click on the OK button. The Solver Add-in will be added to the Data tab.
How to Define and Formulate the Linear Programming Problem?
A linear programming problem consists of an objective function and some constraints. The objective function can be maximized or minimized. Here, we will solve the following linear programming model which has an objective function Z, which we want to maximize, and 3 different constraints for the X1, X2, and X3 variables.
How to Tabulate Linear Programming Problems in Excel?
We have to format the coefficients of the objective function and constraints in a tabular format to solve the linear programming model with Excel Solver Add-in.
- Now, tabulate the linear programming model in the following format to input the objective functions and constraints in the Excel Solver Add-in. Here, the Light Green colored boxes are kept empty for calculations and solutions.
First, insert the following formula in cell F5. Since the range C5:E5 is empty now, cell F5 shows 0. This value indicates the current value of the objective function.
=SUMPRODUCT(C5:E5,C6:E6)
Afterward, for the constraints, apply the following formula in cell F8 >> press Enter key >>Â drag the Fill Handle icon down for the remaining constraint values.
=SUMPRODUCT($C$5:$E$5,C8:E8)
How to Solve the Problem Using Excel Solver?
After tabulating the problem, we can apply the Solver Add-in to solve our linear programming model.
- Now, to solve the model, go to the Data tab >> click on the dropdown menu of the Analysis ribbon >> select the Solver option.
Click the image for a detailed view
- In the Solver Parameters user form, select the Set Objective parameter as cell $F$5 >> click on the radio button of the Max option for the To parameter >> select the range $C$5:$E$5 for the By Changing Variable cells parameter >> click on the Add button to add constraints.
- In the Add Constraint user form, set the Cell Reference to $F$8 >> select >= operator for the first constraint >> set the Constraint to $H$8 >> click on the Add button to similarly add constraint 2 and 3 >> click on the OK button.
- We can see the added constraints in the Subject to the Constraints list box. Now, click on the dropdown menu of the Select a Solving Method option >> select Simplex LP >> click on the Solve button.
We will get our required values for the variables and objective function for the linear programming maximization problem.
Here, the maximized value of the objective function is 71, and the values for the variables X1, X2, and X3 are 0, 11.76, and 4.12 respectively for this solution.
Read More: How to Find Optimal Solution with Linear Programming in Excel
How to Save and Load Solver Scenarios in Excel?
We can save and/or load any solved linear programming model variable values to view or reuse them later.
What Is the Process to Save Linear Programming Model?
- To save a linear programming model, select a range to input the variable values >> go to the Data tab >> click on the Solver option.
Click the image for a detailed view
- In the Solver Parameters user form, we can view the last solved model. Click on the Load/Save button.
- In the Load/Save Model user form, provide the reference of the first cell in the selected range (cell $J$3 in this case) >> click on the Save button.
Click the image for a detailed view
We will get the linear programming model variable values in the selected range that we can view or reuse later.
Click the image for a detailed view
What Is the Process of Loading the Saved Model?
- Similar to the previous example, we need to click on the Load/Save button from the Solver Parameters user form >> select the range holding the previous model ($J$3:$J$9 in this case) >> click on the Load button.
Click the image for a detailed view
- In the Load Model user form, click on the Replace button.
Click the image for a detailed view
This will open the Solver Parameters user form and replace the existing model parameters with the loaded model’s parameters. All we need to do is to click the Solve button to recalculate it.
How to Do Linear Programming Using Graphical Method in Excel?
If a linear programming problem consists of only two variables, then we can use a graphical method to solve that model.
How to Define and Formulate the Problem?
Like the previous example, we also have to define an objective function and some constraints for the linear programming problem. Here, we will solve the following linear programming model which has an objective function Z, which we want to maximize, and 2 different constraints for the X and Y variables.
How to Tabulate the Linear Programming Problem in Excel?
To start, we have to tabulate the linear programming model in the following format using the variable coefficients.
Read More: How to Do Linear Programming in Excel
How to Plot a Chart Using the Constraints?
Although the given constraints are inequality constraints, we will consider them as equality constraints while plotting charts and later use the inequality operators to find the feasible region.
Now, we have to calculate the X and Y-axis intersection points for each constraint. This can be accomplished by setting one variable equal to zero. For example, for the first constraint, if Y = 0 then X = 7, and if X = 0 then Y = 7.
- We have to list these intersection points for each constraint in the following format.
- Now, select the intersection points of constraint 1 >> go to the Insert tab >> click on the dropdown menu of the Scatter option >> select the Scatter with Smooth Lines chart.
Click the image for a detailed view
- A chart like the following will appear.
- Select the chart and right-click on your mouse >> click on the Select Data option.
- From the Select Data Source user form, select Series 1 and click on the Edit button.
- In the Edit Series user form, set the Series Name to range $B$9:$C$9 and press the OK button.
- Afterward, click on the Add button.
- Set the Series Name to range $E$9:$F$9 >> Series X Values to $E$11:$E$12 >> Series Y Values to $F$11:$F$12 >> click on the OK button.
- Click on the OK button in the Select Data Source user form.
- The chart for constraint lines is shown below:
- Select the chart and click on the plus (+) icon to view the Chart Elements >> check the checkbox of Data Labels >> click on the right side of the Data Labels to see additional options >> click on the Data Callout option to see X and Y-axis intersection points >> uncheck Gridlines option to remove them.
Click the image for a detailed view
How to Determine the Coordinates of Each Corner Point in the Feasible Region?
Since both the constraints have <= (less than or equal to) operator and X, Y values are greater than or equal to zero, the marked ABCD region is our feasible region. (Here we have manually placed A B C D to show the corner points)
Now, we need to list all the corner points (A, B, C, and D) of the feasible region and calculate the objective function value in those points. We can get the X and Y values for A, B, and D from the Data Callouts.
- To calculate X and Y values for point C, we can apply the following formula in cell C17.
=TRANSPOSE(MMULT(MINVERSE(C6:D7),F6:F7))
Here, the MINVERSE function calculates the inverse matrix of the matrix formed with X and Y value coefficients in constraints (range C6:D7). And the MMULT function multiplies that inverse matrix with another matrix (range F6:F7). Finally, the TRANSPOSE function converts the rows into columns and columns into rows for the output returned by the MMULT function.
Read More: How to Find Optimal Solution with Linear Programming in Excel
How to Calculate the Value of the Objective Function for Each Corner Point?
Finally, we can calculate the value of the objective function at each corner point.
- Apply the following formula in Cell E15 >> press the Enter key >> use the Fill Handle tool.
=SUMPRODUCT($C$5:$D$5,C15:D15)
As we can see, the maximum value has occurred in point C. And the value of the maximized objective function is 23.
Read More: How to Solve Blending Linear Programming Problem with Excel Solver
What Are the Things to Remember?
- Graphical method can deal with only two variables, whereas Excel Solver can deal with up to 200 variables.
- You can save and/or load any solved linear programming model variable values to view or reuse them later.
- While applying the MMULT function, the matrices should be sequenced properly as the matrices have to follow the multiplication rule.
Frequently Asked Questions
1. What are the limitations of using Excel Solver for Linear Programming?
Answer: Although Excel Solver Add-in is a powerful tool, it has many limitations. Some of these are listed below:
>> suited for small to medium-sized linear programming problems (up to 200 variables)
>> operates with limited precision (up to 15 decimal points)
>> operates with very few numbers of algorithms (Simplex Method for Linear Programming Problems), etc.
2. Can I use the graphical method for solving Linear Programming Problems with 3 variables?
Answer: If we apply the graphical method for Linear Programming problems with 3 variables, the feasible region will be a 3-dimensional space. Since representing 3-dimensional space on a two-dimensional plane can be complex and visually overwhelming, the graphical method is not suitable for Linear Programming problems with more than two variables.
3. Are there any alternative tools or software besides Excel Solver for solving Linear Programming problems?
Answer: Besides the Excel Solver add-in, some alternative tools or software used for solving Linear Programming problems are:
>> IBM ILOG CPLEX optimizer
>> MATLAB Optimization Toolbox
>> GNU Linear Programming Kit
>> PuLP, etc.
Conclusion
This concludes our article on learning Excel Linear Programming. We discussed the process of solving Linear Programming models of multiple variables using Excel Solver. We also discussed how to save a solved model and load it later for reuse. For linear programming problems of two variables, we also discussed the graphical method. We hope the demonstrated methods were able to fulfill your quest for learning about Linear Programming in Excel. Feel free to share your thoughts on the article in the comment box.
Excel Linear Programming: Knowledge Hub
- How to Solve Integer Linear Programming in Excel
- How to Calculate Shadow Price Linear Programming in Excel
- How to Perform Mixed Integer Linear Programming in Excel
- How to Solve Transportation Problem with Linear Programming
- How to Do Linear Programming with Sensitivity Analysis in Excel
- How to Graph Linear Programming in Excel
<< Go Back to Solver in Excel | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
Excel Linear Programming (Using Solver and Graphical Methods)
I thought this article was made extremely well. It showed how to install the necessary tools, how to use linear programming on excel (step by step) and also showed other excel solver alternatives such as IBM ILOG CPLEX optimizer. The only thing that I would like added to this article is an example along with the step-by-step tutorial just to add context to the steps. I would like to apply these models in figuring out problems such as how many units of product A and B should we produce to maximize profit?
Hello Martin,
We are glad to hear that you found this article useful! Including a real-life example in tutorials can indeed make the steps more relatable and easier to understand. Applying these models to specific scenarios like maximizing profit by determining the production units for products A and B is a great way to leverage linear programming. We will include this in our next update.
Regards
ExcelDemy