Solve linear programming problem using Excel

Today we’ll be learning how to solve Linear Programming problem using MS Excel? Linear programming (LP) is useful for resource optimization. There are so many real life examples and use of linear programming. We’ll see one of the real life examples in the following tutorial.

Modelling Linear Programming

As the first step we have to do the modelling i.e. make the required equation. We can find the constraints in the right side (column N). Cell F4 is our equation P which has to be minimized and F6,F7,F8 are the constraints.

1-min

Solving the linear model using Excel Solver

Now go to Data and open solver. If you don’t find the Solver option please check How to enable Solver in MS Excel.

2-min

Solver window will appear. Now we have to follow the steps mentioned below-

1. Set objective is our equation which has to minimized here cell F4, 
2. Set To = Min,
3. Changing variables are x and y i.e. D3 and E3,
4. add constraints using Add option.
Please check image below for reference.

3-min

Add constraint window will appear once Add option clicked. Be careful while adding constraints and the sign (greater or less than). Once the constraints are added click OK.

4-min

Once everything is done Solver parameters will look like below and click Solve. That’s done.

4a-min

We’ll see that now we have values for x and y and P. This was optimize solution for the problem.

5-final-min

 

Problem Source

So this was about how to solve linear programming in MS Excel using Solver add-in. Keep visiting Analytics Tuts for more tutorials. Comment your suggestions and doubts.

 

3 comments

  • A problem i have been struggling with for about a month was just solved in less than an hour as i come across this site. i bless my Lord my Jesus for this an recommend it for anyone carrying out research or studies on solving mathematical problems using excel

  • I use the SUP function in excel 2013 it was working well but at the end of the table is no longer give me the results, the cell still empty.

  • thanks for your contribution on the knowledge of MS EXCEL with LPP , its wonderful i never know before now that excel can do that, genius!

Leave a Reply

Your email address will not be published. Required fields are marked *