Create Presentation
Download Presentation

Download Presentation
## Solving Equations with Excel Solver for Microsoft Excel 2000

- - - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - - -

**Solving Equations with Excel Solver for Microsoft Excel 2000****A Simple Problem**• grades, grades, grades*… • You would love to have a quiz average of 80 points (or better) in the QTM1300 course by the end of the semester. • Your quiz scores on the first eight quizzes are given in the table below. • What scores do you need to earn on the 9th and 10th final quizzes in order to meet your goal average of 80 points? * from FCL Excel Notes by Joyce Gordon, Babson College, 1998.**Quiz Average**90 + 88 + 78 + 68 + 84 + 82 + 66 + 74 + x + y = 80 10 Formulate the Problem • obtain equation to be solved You are looking for x = score on 9th quiz (0 x 100) y = score on 10th quiz (0 y 100) so that NOTE: When using Excel Solver to solve an equation make sure the right-hand side is a constant.**Preparing the Worksheet for Solver**• start with a blank sheet**Preparing the Worksheet for Solver**• enter labels and constants Enter labels in cells A1:A13 and B1**Preparing the Worksheet for Solver**• enter labels and constants NOTE: These labels are not essential for the use of Solver**Remember…**• Quiz • 1 • 2 • 3 • 4 • 5 • 6 • 7 • 8 • Score • 90 • 88 • 78 • 68 • 84 • 82 • 66 • 74 Preparing the Worksheet for Solver • enter labels and constants Enter constants in cells B2:B9**Remember…**90 + 88 + 78 + 68 + 84 + 82 + 66 + 74 + x + y = 80 10 x y Preparing the Worksheet for Solver • enter formula Variable values in cells B10 and B11 Left-hand side of equation in cell B13 NOTE: These cells will be colored to indicate that they are essential for Solver**Remember…**90 + 88 + 78 + 68 + 84 + 82 + 66 + 74 + x + y = 80 10 Preparing the Worksheet for Solver • enter formula Click on cell B13**Remember…**90 + 88 + 78 + 68 + 84 + 82 + 66 + 74 + x + y = 80 10 Preparing the Worksheet for Solver • enter formula Type in =AVERAGE(**Remember…**90 + 88 + 78 + 68 + 84 + 82 + 66 + 74 + x + y = 80 10 Preparing the Worksheet for Solver • enter formula Highlight cells B2:B11**Remember…**90 + 88 + 78 + 68 + 84 + 82 + 66 + 74 + x + y = 80 10 Preparing the Worksheet for Solver • enter formula Type in ) and hit Enter**Preparing the Worksheet for Solver**• enter formula NOTE: Alternative formula for cell B13: =(B2+ B3+ B4+ B5+ B6+ B7+ B8+ B9+ B10+ B11)/10**Preparing the Worksheet for Solver**• enter formula NOTE: The function AVERAGE entered in cell B13 returns the average of the values in cells B2:B11; blank cells are ignored**Using Solver**• invoke Solver Click on Tools**Using Solver**• invoke Solver Click on Solver**Using Solver**• invoke Solver**NOTE: The cell displayed in the Set Target Cell: box must**contain the formula for the left-hand side of the equation (cell B13) Using Solver • complete the Solver Parameters dialog box Click on cell B13**Check the Value of: circle**Using Solver • complete the Solver Parameters dialog box**Click on the Value of: box**Using Solver • complete the Solver Parameters dialog box NOTE: The value displayed in the Value of: box must be the constant on the right-hand side of the equation (80)**Using Solver**• complete the Solver Parameters dialog box Type in 80 NOTE: The value displayed in the Value of: box must be the constant on the right-hand side of the equation (80)**Click on the By Changing Cells: box**Using Solver • complete the Solver Parameters dialog box**Using Solver**• complete the Solver Parameters dialog box Highlight cells B10:B11 NOTE: The cells displayed in the By Changing Cells: box must be the cells containing variable values (cells B10:B11)**Click on Add**Using Solver • complete the Solver Parameters dialog box NOTE: The Subject to the Constraints: box must contain the constraints on the variable values (x,y 100)**Click on the Cell Reference: box**Using Solver • complete the Solver Parameters dialog box Highlight cells B10:B11 NOTE: The Subject to the Constraints: box must contain the constraints on the variable values (x,y 100)**Make sure <= is displayed**Using Solver • complete the Solver Parameters dialog box NOTE: The Subject to the Constraints: box must contain the constraints on the variable values (x,y 100)**Click on the Constraint: box and type in 100**Using Solver • complete the Solver Parameters dialog box NOTE: The Subject to the Constraints: box must contain the constraints on the variable values (x,y 100)**Click on OK**Using Solver • complete the Solver Parameters dialog box NOTE: The Subject to the Constraints: box must contain the constraints on the variable values (x,y 100)**Click on Options**Using Solver • set the Options**Check the Assume Linear Model and the**Assume Non-Negative boxes Using Solver • set the Options NOTE: The equation (in the Target Cell B13) is linear on the non-negative variables (x in B10 and y in B11)**Accept the remaining default options by clicking on OK**Using Solver • set the Options NOTE: The equation (in the Target CellB13) is linear on the non-negative variables (x in B10 and y in B11)**Click on Solve**Using Solver • execute Solver**Using Solver**• read solution You need to earn a score of 100 on the 9th quiz and 70 on the 10th quiz to meet your goal average of 80 points.**Click on OK**Using Solver • end execution**You need algebra!**Using Solver • a final comment COMPLETESOLUTION You need to earn a score of x on the 9th quiz, where 70 x 100, and 170 – x on the 10th quiz to meet your goal average of 80 points. NOTE:Excel Solver might not find all possible solutions