How To Solve Linear Programming In Excel

Linear programming is a mathematical method of optimizing an outcome in a mathematical model using linear equations as constraints. To solve a standard form linear program use Microsoft Excel and the Excel Solver add-in. Excel Solver can be enabled in Excel 2010 by clicking "file" in the toolbar, "options," and "Add-in." Check the "Solver Add-in" option, and click "OK." You can access Solver under the "Data" tab in the toolbar. The most basic linear program to solve is standard form.

Step 1

Set up the linear program in the form:

Maximize c(transpose)x Subject to: Ax ≤ b, x ≥ 0

where c, x, A, and b are matrices. The objective function can also be minimized or equal to some number z. The constraints are in linear form. X does not have to have a non negative constraint. These differences in the linear program depend on the specific problem. However, it is imperative that the linear program be set up correctly. Be sure to make all calculations for the cTx, Ax, and b matrices in Excel before you solve the linear program. You can begin by either setting all values of x to 1 or leaving them unknown. It can be helpful to name the cells by clicking "Insert" in the toolbar, "Name," and "Define." The names of the cells can by typed into Solver directly.

Step 2

Open Solver and input the necessary cells. In order to input a cell, click on the Excel icon to the right of the text box, and then click on the desired cell. The "Set Target Cell:" is the objective function. "By changing Cells:" are the variables in your linear program, which is the x matrix. Click on "Add" to add a constraint. The cell reference is the Ax matrix. Choose the type of constraint (greater than or equal to, less than or equal to, or equal to) from the pull down menu. The constraint is the b matrix. If x is non-negative, add this constraint for each x value.

Step 3

Choose a correct linear model from the "Select a Solving Method:" pull down menu. Standard form linear programs generally use a LP Simplex solving method. If x has a non-negative constraint, check the box "Make Unconstrained Variables Non-Negative."

Step 4

Solve the linear program by clicking on "Solve." Allow Solver to think for a moment. If Solver finds a solution a dialogue box with the title "Solver Results" will pop-up. You are given the choice of keeping the solver solutions or restoring all cells to their original value.