{[ promptMessage ]}

Bookmark it

{[ promptMessage ]}

Excel Solver Tutorial

Excel Solver Tutorial - ENGR62/MS&E111 Prof Ben Van Roy...

This preview shows pages 1–4. Sign up to view the full content.

ENGR62/MS&E111 Spring 2008 Prof. Ben Van Roy April 1, 2008 EXCEL SOLVER TUTORIAL This tutorial will introduce you to some essential features of Excel and its plug-in, Solver, that we will be using throughout ENGR62 to solve linear programs (LPs). You will learn how to represent LPs in an Excel worksheet, use the matrix functions SUMPRODUCT and MMULT , set up an LP in Solver, and generate various outputs from Solver. This quarter you will see many applications of Solver in class demonstrations and homework as- signments. Keep this tutorial document as a reference as you review the class demos and as you formulate LPs in your homeworks. For additional Excel/Solver assistance, try consulting the Excel Help files or numerous online resources. Parts of this tutorial borrow from Prof. Veinott’s detailed Formulating and Solving Linear Pro- grams in Excel . 1 Setting Up Linear Programs In Excel Linear programs (LPs) can take many forms. In ENGR62 we will often be dealing with LPs that can be represented mathematically as maximize c T x subject to Ax b, where the matrices A , b , and c comprise the problem data . Our goal is to maximize the value of the objective function, c T x , by choosing an appropriate vector of decision variables, x . The matrices A and b specify a set of linear inequality constraints that reflect the requirements of the problem. Entering Matrices into Excel It is easy to display the components of an LP in their natural way in Excel, i.e., as a range of cells. For example, if A is a × 3 matrix, then each element of A can be entered into its corresponding cell in a 5 × 3 range of cells. Identify ranges of cells to contain the data matrices A , b , and c and enter them into the Excel worksheet. Do the same for the decision variables, x . To keep your worksheet organized, you might consider arranging the matrices as shown below. You are also encouraged to use labels and text to document your work.

This preview has intentionally blurred sections. Sign up to view the full version.

View Full Document
EXCEL SOLVER TUTORIAL Page 2 of ?? a 11 a 12 a 13 a 21 a 22 a 23 a 31 a 32 a 33 a 41 a 42 a 43 a 51 a 52 a 53 Each matrix element occupies a cell in the worksheet. c T c T x A x Ax b A sample layout of LP matrices in an Excel worksheet.
EXCEL SOLVER TUTORIAL Page 3 of ?? Naming Matrices in Excel Excel allows you to name ranges of cells in the worksheet. This feature is especially convenient for doing matrix calculations and for setting up LPs in Solver. To name a range of cells, select the entire range with the mouse and use the Insert Name Define... menu. Alternatively, select the range and enter its name in the Name Box adjacent to the Formula Bar. Try using both methods to give names to the data matrices A , b , and c and to the decision variables, x .

This preview has intentionally blurred sections. Sign up to view the full version.

View Full Document
This is the end of the preview. Sign up to access the rest of the document.

{[ snackBarMessage ]}