EDUCBA

EDUCBA

MENUMENU
  • Blog
  • All Courses
  • All in One Bundle
  • Login
Home Excel Excel Resources Statistical Functions in Excel Linear Programming in Excel

Linear Programming in Excel

By Madhuri ThakurMadhuri Thakur

Linear Programming in Excel

Excel Linear Programming (Table of Contents)

  • Introduction to Linear Programming in Excel
  • Methods to Solve Linear Programming through Excel Solver

Introduction to Linear Programming in Excel

Linear Programming is most important as well as a fascinating aspect of applied mathematics which helps in resource optimization (either minimizing the losses or maximizing the profit with given resources). If we have constraints and the objective function well defined, we can use the system to predict an optimal solution for a given problem. In Excel, we have Excel Solver, which helps us solving the Linear Programming Problems a.k.a. LPP. We will see in this article how to use Excel Solver to optimize the resources associated with business problems with the help of Linear Programming.

Start Your Free Excel Course

Excel functions, formula, charts, formatting creating excel dashboard & others

First thing first. Let’s see how we can enable Excel Solver (a key component of LPP under Excel).

Methods to Solve Linear Programming through Excel Solver

Let’s understand how to use Linear Programming through excel solver with some methods.

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

Method #1 – Enabling Solver under Microsoft Excel

In Microsoft Excel, we can find Solver under Data tab which can be found on the Excel Ribbon placed at the upper most part as shown below:

Data -solver 1

If You can’t see this utility tool there, you need to enable it through Excel Options. Follow the steps below to enable Solver under Excel.

Step 1: Navigate towards the File menu and click on Options, which is the last thing on that list.

Options-solver

Step 2: A new window will pop up named Excel Options. Click on Add-ins from the list of options present on the left-hand side in the window.

Add-ins solver

Step 3: Under the Manage section at the bottom of the window, select Excel Add-ins from the dropdown list and click on Go… button placed beside.

excel add ins

Step 4: As soon as you click on Go… button, you will be able to see the list of all Add-ins available under excel in a new window. Tick to select the Solver Add-in so that you can use it under the Data tab for solving the equations. Click the OK button after tick selecting the Solver Add-in.

solver add in 1

This way, you can enable the Excel Solver under Microsoft Excel.

Method #2 – Solving Linear Programming Problem using Excel Solver

Now, we will try to solve the linear programming problem using the Excel Solver tool.

Example: A chemical plant produces two product:: A. These two products need raw materials as shown below: Product A needs three types of raw materials – Material_1 20KG, Material_2 30KG, Material_3 as 5 KG. On similar lines, Product B requires 10KG of Material_1, 30 KG of Material_2 and 10 KG of Material_3. The manufacturer requires a minimum of 460KG or Material_1, 960KG of Material_2 and 220KG of Material_3. If the per-unit cost for Product A is $30 and the cost of Product B is $35, how many products the manufacturer should blend to meet the minimum material requirements at the lowest cost possible? Let’s use the information provided under this example for modelling the equations.

Step 1: We can see all the equation constraints we can form using the information provided in the example above.

Linear Programming in Excel 2-1

Step 2: Use these equations to add the constraints cell-wise under Excel across A2:C8 of the given sheet. See the screenshot as below:

Linear Programming in Excel 2-2

Step 3: Now, we need to use the formula Quantity * Per Unit Cost and sum it up for both the products to get the actual material requirements. You can see this formulated under column D for all cells containing constraints B3, B4, C3). Please see the attached screenshot below:

Linear Programming in Excel 2-3

If you will have a closer look at this formula, we have used B3 and C3 as fixed members for each formula across the different cells in column D. This is because B3 and C3 are the cells denoting quantities for Product A and Product B, respectively. These quantities will appear once the system of equations is solved using Excel Solver.

Step 4: Click on Data tab and then on Solver which is present under Analyze section in the tab.

Linear Programming in Excel 2-4

Step 5: Once you click on Solver, a new tab named “Solver Parameter” will open up, under which you need to set the parameters for this set of the equation to be solved.

Linear Programming in Excel 2-5

Step 6: The first thing we need to identify is Set Objective: Since our objective is to figure out the total cost involved so that it can be minimized, set this to D4.

Linear Programming in Excel 2-6

Step 7: Since we need to minimize the cost with the highest production possible, set the next parameter as Min. You can get this done by clicking on the Min radio button.

Linear Programming in Excel 2-7

Step 8: under By Changing Variable Cells:, we need to mention B3 and C3 since these cells will be the one’s containing Quantities for Product A and Product B, respectively after the problem gets solved.

set objective 2

Step 9: Now add the constraints. Click on Add button under the Subject to the Constraints: section, and it will open up a new window to add constraints. Under that window – B3:C3 as Cell Reference, >= and 0 as Constraints. This we are doing since the basic constraint in any LPP is that, X and Y should be greater than zero.

Add constraint

Step 10: Click again on the Add button and this time use B3:C3 as Cell Reference and F6:F8 as Constraints with inequality as >=. Click the OK button to add this constraint as well under the solver.

Add constraint 1

Solver now has all the parameters required to solve this set of linear equations, and it looks as below:

subject 1

Step 11: Now, click on Solve button at the bottom of the window to solve this linear equation and come up to the optimal solution.

Solve parameter 1-2

As soon as we click on the solve button, the system starts searching for an optimal solution for the problem we have provided, and we get the values for B3, C3 using which we also get the values under column F for F4, F6:F8, which are the optimal costs and material values that can be used for Product A and Product B.

producr 1

This solution informs us that if we need to minimize the cost of Production for Product A and Product B with optimal usage of Material_1, Material_2, and Material_3, we should produce 14 quantities of Product A and 18 Quantities of Product B.

This is it from this article. Let’s wrap the things up with some points to be remembered:

Things to Remember About Linear Programming in Excel

  • It is mandatory to solve Linear Programming Problems using Excel Solver. There is no other method we can do this using.
  • We should always have constraints and object variable to be set ready with us.
  • If Solver is not enabled, you can enable it under Excel Add-in options.

Recommended Articles

This is a guide to Linear Programming in Excel. Here we discuss How to use Linear Programming in Excel along with practical examples and a downloadable excel template. You can also go through our other suggested articles –

  1. Interpolate in Excel
  2. Programming in Excel
  3. Linear Interpolation in Excel
  4. Linear Regression in Excel
MICROSOFT POWER BI Training
48+ Hours of HD Videos
8 Courses
Verifiable Certificate of Completion
Lifetime Access
4.5
MS Excel & VBA for Data Science
128+ Hours of HD Videos
24 Courses
10 Mock Tests & Quizzes
Verifiable Certificate of Completion
Lifetime Access
4.5
All-in-One Financial Analyst Masters Training Program
1000+ Hours of HD Videos
43 Learning Paths
250+ Courses
Verifiable Certificate of Completion
Lifetime Access
4.9
All in One Data Science Bundle (360+ Courses, 50+ projects)
1500+ Hour of HD Videos
80 Learning Paths
360+ Courses
Verifiable Certificate of Completion
Lifetime Access
4.7
Primary Sidebar
Popular Course in this category
PROGRAMMING LANGUAGES Course
 502+ Hours of HD Videos
54 Courses
4 Mock Tests & Quizzes
  Verifiable Certificate of Completion
  Lifetime Access
4.5
Price

View Course
Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign Up
  • Live Classes
  • Corporate Training
  • Certificate from Top Institutions
  • Contact Us
  • Verifiable Certificate
  • Reviews
  • Terms and Conditions
  • Privacy Policy
  •  
Apps
  • iPhone & iPad
  • Android
Resources
  • Free Courses
  • Excel Charts
  • Excel Tips
  • All Tutorials
Certification Courses
  • All Courses
  • Excel VBA Course - All in One Bundle
  • VBA Course
  • Excel Data Analysis Course
  • Excel for Marketing Course
  • Excel for Finance Course
  • Excel for HR Training

ISO 10004:2018 & ISO 9001:2015 Certified

© 2023 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

EDUCBA
Free Excel Course

Excel functions, formula, charts, formatting creating excel dashboard & others

By continuing above step, you agree to our Terms of Use and Privacy Policy.
*Please provide your correct email id. Login details for this Free course will be emailed to you
Let’s Get Started

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA

Download Linear Programming Excel Template

EDUCBA Login

Forgot Password?

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA

Download Linear Programming Excel Template

EDUCBA

डाउनलोड Linear Programming Excel Template

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more