logo

Solving Linear Programming Problems with Excel 📂Optimization

Solving Linear Programming Problems with Excel

Guide

Step 1. Activate the Solver Add-in

Additional Features.webp

In the File/Options/Add-ins tab, click the Go(G) button next to Excel Add-ins in Manage(A). A window named Add-ins will pop up like below.

20211230_142633.png

Check the Solver Add-in and click OK.

20211230_142847.png

The Solver feature has been activated under the Data tab in Analysis.

Step 2. Transcribe the Linear Programming Problem

Maximizex1+x2subject to−x1+x2≤1x1≤3x2≤2 \begin{matrix} \text{Maximize} & & x_{1} & + & x_{2} \\ \text{subject to} &-& x_{1} & + & x_{2} & \le & 1 \\ & & x_{1} & & & \le & 3 \\ & & & & x_{2} & \le & 2 \end{matrix}

Let’s solve the following maximization problem as a simple example in x1,x2≥0x_{1} , x_{2} \ge 0. The Shrimp Sushi Restaurant tried to solve this problem by hand using the Simplex Method and knew the answer as (x1∗,x2∗)=(3,2)\left( x_{1}^{\ast}, x_{2}^{\ast} \right) = (3,2). This linear programming problem can be transcribed as follows.

20211230_144239.png

B1 should be filled with x1x_{1}, and B2 with x2x_{2}. To represent the constraints, fill in the formulas below into B3~B6.

B3: =B1+B2
B4: =-B1+B2
B5: =B1
B6: =B2

Currently, B1 and B2 are empty, thus calculated as 0. C4 to C6 were filled with the right-hand side of the constraints.

Step 3. Find Solution

20211230_151158.png

Clicking on the Solver as in Step 1, the ‘Solver parameters’ window appears like above.

20211230_151224.png

In Set Objective(T), input $B$3. This is our objective function.

20211230_151240.png

In By Changing Variable Cells(B), input $B$1:$B$2. These are our variables x1,x2x_{1}, x_{2}.

20211230_151319.png

Add the constraints in Subject to the Constraints(U).

20211230_151331.png

Check the Make Unconstrained Variables Non-Negative(K) and select Simplex LP in Select a Solving Method(E).

20211230_151353.png

Finally, pressing the Solve(S) button will solve the optimization problem as shown above. It matches the answer (3,2)(3,2) we knew from theoretical calculation.

See Also