Linear Programming in Excel

Article byJeevan A Y
Edited byAshish Kumar Srivastav
Reviewed byDheeraj Vaidya, CFA, FRM

Linear Programming in Excel using Solver

Linear programming is one of the important concepts in statistics. Based on available data of variables, we can do predictive analysis. In our earlier article, “Linear Regression in Excel,” we have discussed it in detail. However, in Excel, we have an option called “Solver in Excel,” which can be used to solve a linear programming problem. With this solver, we can use linear programming to enable resource optimization.

This article will show you how to solve the linear programming problem in Excel in detail. Follow the entire article to learn about this.

Linear-Programming-in-Excel

You are free to use this image on your website, templates, etc, Please provide us with an attribution linkHow to Provide Attribution?Article Link to be Hyperlinked
For eg:
Source: Linear Programming in Excel (wallstreetmojo.com)

How to Solve Linear Programming through Excel Solver?

To apply a solver to solve linear programming, we should have an ethical problem in detail. For this example, we have created the below scenario.

You can download this Linear Programming Excel Template here – Linear Programming Excel Template

Problem: A manufacturer wants to alter the production model of the current product. He has two kinds of products, “Product 1” & “Product 2”. For Product 1 requires three raw materials, Raw Material 1 20 Kg, Raw Material 2 30 Kg, and Raw Material 3 5 Kg. Similarly, for Product 2, it requires three raw materials, Raw Material 1 10 Kg, Raw Material 2 25 Kg, and Raw Material 3 10 Kg.

Manufactures require a minimum of Raw Material 1 – 550 kg, Raw Material 2 800 kg, and Raw Material 3 250 kg. If Product 1 costs Rs. 30 per unit and Product 2 costs 35 per unit, how many units of each product should the manufacturer blend to meet the minimum raw material requirements at a low cost as possible, and what is the price?

Now, we enter all this information into the Excel spreadsheet below.

Linear Programming Example 1

We need to apply the excel formulaExcel FormulaThe term "basic excel formula" refers to the general functions used in Microsoft Excel to do simple calculations such as addition, average, and comparison. SUM, COUNT, COUNTA, COUNTBLANK, AVERAGE, MIN Excel, MAX Excel, LEN Excel, TRIM Excel, IF Excel are the top ten excel formulas and functions.read more in cells D3 and D5 to D7, Cost * Cost Per Unit. Cost price we need to arrive from the solver in cells B2 and C2. To use the formula below:

Linear Programming Example 1-1

After setting up this, we need to go to Excel’s “Solver” tool. The “Solver” tool is available under Excel’s “Data” tab.

–>> If you want to learn Excel and VBA professionally, then ​Excel VBA All in One Courses Bundle​ (35+ hours) is the perfect solution. Whether you’re a beginner or an experienced user, this bundle covers it all – from Basic Excel to Advanced Excel, Macros, Power Query, and VBA.

Enable Solver Add-in

Linear Programming Example 1-2

If the spreadsheet does not show this option, we need to enable it. To allow this solver option, we must follow the below steps.

  • Step 1: We must first go to the “File” tab. Then, under the “File tab,” click on “Options.”
Linear Programming Example 1-3
  • Step 2: Next, go to “Add-ins” under Excel “Options.”
Linear Programming Example 1-4
Linear Programming Example 1-5
  • Step 4: Under the pop-up below, choose “Solver Add-in” and click on “OK” to enable it.
Linear Programming Example 1-6

Now, we can see “Solver Add-in” under the “Data” tab.

Solve Linear Programming through Excel Solver

  • Go to the “DATA” tab to apply solver to apply the solver and click on the “Solver.” We can see below the window.
Example 1-7

In the above window, our first option is “Set Objective.”

  • Our objective is to identify the “Total Cost,” so our total cost cell is D3, so we must select the cell D3 for this “Set Objective” and set it to “Min.”
Linear Programming Example 1-8
  • The next option is “By Changing Variable Cells.” In this example, our variables are “Product 1” and “Product 2”. To select a range of cell B2:C2 and click on “Add.”
Example 1-9
  • Once we click on “Add,” we can see below the add “Constraint” window. Select the B2:C2 range of cells in this window and put the constraint as “>=0”.
Example 1-10
  • Click on “Add” to stay back in the same window. Now in the second constraint, select the range of values as D5:D7 and select “>=” and under “Constraint,” select G5:G7 cells.
Linear Programming Example 1-11
  • Click on “OK” to come out of the “Add Constraint” window.
Linear Programming Example 1-12
  • Now, all our parameters are ready. Click on the “Solve” option to get the result.
Example 1-13
  • So, the cost to produce “Product 1” per unit is 20, and “Product 2” per unit is 15.
Example 1-14

Like this, by using “Solver,” we can solve linear programming in Excel.

Things to Remember

  • By default, the “Solver” tool is not available to use.
  • A solver is not only limited to a linear programming language, but we can also solve many other problems. You may refer to our article “Solver Option in Excel.”
  • Setting the objective cell is important.
  • The adding constraints should be ready in advance.

This article is a guide to Linear Programming in Excel. We discuss how to solve the linear programming problem in Excel using the solver option with an example and a downloadable Excel template. You may learn more about Excel from the following articles: –