Disclosure: This post may contain affiliate links, meaning when you click the links and make a purchase, we receive a commission.

How to Graph Linear Programming in Excel (with Detailed Steps)

At times, we need to solve multiple variables through linear programming. There are several ways to solve linear programming. The handiest method to solve linear programming is through graphing. In this article, I will show you detailed steps to graph linear programming in Excel.


Download Practice Workbook

You can download our practice workbook from here for free!


What Is Linear Programming?

Linear programming is a mathematical tool to analyze a situation through several mathematical functions and constraints and find the optimum point of your objective. This technique is vastly used in optimizing business investment, production cycle, in terms of buying our required products, etc.


Basic Components of Linear Programming

  • Decision Variables: These are the variables that are needed to calculate the optimum point of our objective through linear programming. The situation of our decisions, constraints and objective function are set with these variables.
  • Constraints: Constraints are the conditions that limit the objective function and determine the feasible region. They can be both equalities or inequalities.
  • Objective Function: This is the function of your objective. You have to satisfy this equation with proper constraints to find the optimal solution.
  • Feasible Region: This region is the optimal region of the objective function after applying the proper constraints. The optimal solution lies somewhere in this region.
  • Feasible Solution: Feasible solutions are the solutions of the objective function for the corner points of the feasible region.
  • Optimal Solution: Optimal solution is the optimal point of your objective function. You can find this from the calculated feasible solutions.

Linear Programming Graph


Steps to Graph Linear Programming in Excel

Let’s say, you are given an objective function as F = 6X+8Y. You have to maximize this function with the constraints:

2X+4Y <= 60

4X+2Y <= 48

Now, you can find the optimum point by graphing the linear programming in Excel by using the following steps below.

Required Data to Graph Linear Programming in Excel


📌 Step 1: Record Objective Function & Constraints Line Points

To graph linear programming in Excel, first and foremost, you need to record your objective function and the points of the constraints.

  • To do this, first record the coefficients and symbols of the objective function and constraints properly.

Recording the Functions and Constraints to Graph Linear Programming in Excel

  • Now, for the first constraint, C1, find two points of the equation to draw the constraint. You can do this by taking X=0 which would give you Y as 15. Similarly, taking Y=0 would give X as 30.

Constraint 1 Line's Points

  • Subsequently, find two points for the second constraint, C2, similarly. Here, if you take X=0, you would find Y as 24. And, similarly, for taking Y=0, you would find X=12.

Constraint 2 Line's Points

As a result, you will have a worksheet with your objective function, constraints, and two points for drawing the constraints. The worksheet would look like this finally.

Objective Function with Constraint Points


📌 Step 2: Find Feasible Region

Following the first step, you need to find the feasible region now.

  • To do this, initially, select the B6:C8 cells. Subsequently, go to the Insert tab >> Charts group >> Insert Scatter or Bubble Chart tool >> Scatter with Smooth Lines option.

Insert Scatter Plot to Graph Linear Programming in Excel

  • As a result, you will have a scatter plot with smooth lines according to the values of the B6:C8 cell.

Inserted Plot

  • But, the plot is not in the format that you need. So, right-click on the chart. Subsequently, choose the Select Data… option from the context menu.

Accessing the Select Data Source Window

  • Consequently, the Select Data Source window will appear. Following, choose the series1 option here and click on the Edit button.

Accessing the Edit Series Window

  • At this time, the Edit Series window will appear. At the Series name: text box, write C1. At the Series X values: text box, select the range as B6:B7 cells. Similarly, at the Series Y values: text box, select the range as C6:C7 values. Last but not least, click on the OK button.

Edit Data Series for the Constraint Scatter to Graph Linear Programming in Excel

  • Now, you will be back in the Select Data Source window. Here, click on the Add button.

Adding Another Data Series

  • As a result, another Edit Series window will appear. At the Series name: text box, write C2. Following, at the Series X values: text box, select the data range as B11:B12 cells. Similarly, at the Series Y values: text box, select the data range as C11:C12 cells. Subsequently, click on the Ok button.

Edit the Data Series for Added Constraint Plot

  • Now, you will be back again in the Select Data Source window. Next, click on the OK button here.

Finalize the Plot to Graph Linear Programming in Excel

  • Consequently, you will see you will have a scatter graph with all constraints of your linear programming. And, the graph should look like this.

Constraint Graphs

  • Now, as both the constraints are in less than or equal inequality, both constraint lines will be directed to the origin. As a result, the feasible area would be like the following figure.

Find the Feasible Region to Graph Linear Programming in Excel

Thus, ABCD is the feasible region, and A, B, C, and D are the corner points of the region.


📌 Step 3: Find Optimum Solution

After determining the feasible region, you need to find the feasible solutions now.

  • In doing this, at the very beginning, you have to find the X and Y coordinates of the corner points. From the graph and constraint value table, we can find the A, B and C points very easily that are (0,15), (0,0), and (12,0) respectively.

Feasible Solutions from Graph & Axis

  • Now, for finding the coordinates of point D, select the cells D5:D6 and insert the formula below which involves the MMULT and MINVERSE functions. Subsequently, press Ctrl+Shift+Enter.
=MMULT(MINVERSE('Finding Points of Constraints'!C6:D7), 'Finding Points of Constraints'!F6:F7)

Find the Remaining Feasible Solution through MMULT and MINVERSE Functions

🔎 Formula Breakdown:

  • MINVERSE(‘Finding Points of Constraints’!C6:D7)

This returns the inverse matrix of the Finding Points of Constraints worksheet’s C6:D7 cells’ values.

Result: (-0.166666667, 0.333333333) & (0.333333333, -0.166666667)

  • =MMULT(MINVERSE(‘Finding Points of Constraints’!C6:D7), ‘Finding Points of Constraints’!F6:F7)

This returns the matrix product of the previous result’s array and the Finding Points of Constraints worksheet’s F6:F7 array.

Result: {6,12}

  • As a result, you will get the coordinates of the intersecting point D of the two constraint lines.

Remaining Feasible Solution to Graph Linear Programming in Excel

  • Now, you have all the corner points. At this time, you need to find feasible solutions from these points. For this, write the formula below in the C7 cell. Subsequently, press the Enter button.
=(C5*'Finding Points of Constraints'!$C$5)+('Finding Points of Constraints'!$D$5*C6)

Find Objective Function's Values to Graph Linear Programming in Excel

🔎 Formula Breakdown:
  • =(C5*’Finding Points of Constraints’!$C$5)

This would calculate the multiplication of the C5 cell value and the Finding Points of Constraints worksheet’s C5 cell value.

Result: 0

  • (‘Finding Points of Constraints’!$D$5*C6)

This would multiply the Finding Points of Constraints worksheet’s D5 cell value with the C6 cell’s value of the current worksheet.

Result: 120

  • =(C5*’Finding Points of Constraints’!$C$5)+(‘Finding Points of Constraints’!$D$5*C6)

This would sum up the previous two results.

Result: 120

  • As a result, you will get your objective function’s value for the corner point, A. Following, place your cursor in the bottom right position of your cell. Consequently, the fill handle will appear. Drag it rightward to copy the same formula for all other points.

Drag Fill Handle to Copy Formula

  • As a result, you will get all the feasible solutions.

All Feasible Solution of Linear Programming

  • Last but not least, you will need to find the maximum value of F to solve your linear programming through graph as you are required to maximize the F. Now, as you can see the maximum value of F is 132 at the D (6,12) point. So, your optimum point is the D (6,12) point.

Optimum Solution of Linear Programming

Thus, your linear programming through graph ends and the final result comes.

Read More: How to Solve Blending Linear Programming Problem with Excel Solver


Conclusion

To conclude, in this article, I have shown you all steps in detail to graph linear programming in Excel. I would suggest you go through the full article carefully and practice thoroughly with our practice workbook. I hope you find this article helpful and informative. If you have any further queries or recommendations, please feel free to comment here.
And, visit Excel Demy for many more articles like this. Thank you!


Related Articles

Tanjim Reza

Tanjim Reza

Hello! I am Md. Tanjim Reza Tanim. I have just completed my B.Sc from Naval Architecture & Marine Engineering Department, BUET. Currently, I am working as an Excel & VBA content developer. I always had a great fascination with Microsoft Excel and its cool functions and formulas. Here, I am learning every day about new functions and formulas and working on applying MS Excel to the analysis of our real-life problems. I have great enthusiasm for learning any kind of new things, writing articles, and solving real-life problems.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo