1 / 35

Solving Equations with Excel Solver for Microsoft Excel 2000

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.

MikeCarlo
Télécharger la présentation

Solving Equations with Excel Solver for Microsoft Excel 2000

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Solving Equations with Excel Solver for Microsoft Excel 2000

  2. 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.

  3. 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.

  4. Preparing the Worksheet for Solver • start with a blank sheet

  5. Preparing the Worksheet for Solver • enter labels and constants Enter labels in cells A1:A13 and B1

  6. Preparing the Worksheet for Solver • enter labels and constants NOTE: These labels are not essential for the use of Solver

  7. 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

  8. 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

  9. Remember… 90 + 88 + 78 + 68 + 84 + 82 + 66 + 74 + x + y = 80 10 Preparing the Worksheet for Solver • enter formula Click on cell B13

  10. Remember… 90 + 88 + 78 + 68 + 84 + 82 + 66 + 74 + x + y = 80 10 Preparing the Worksheet for Solver • enter formula Type in =AVERAGE(

  11. Remember… 90 + 88 + 78 + 68 + 84 + 82 + 66 + 74 + x + y = 80 10 Preparing the Worksheet for Solver • enter formula Highlight cells B2:B11

  12. 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

  13. Preparing the Worksheet for Solver • enter formula NOTE: Alternative formula for cell B13: =(B2+ B3+ B4+ B5+ B6+ B7+ B8+ B9+ B10+ B11)/10

  14. 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

  15. Using Solver • invoke Solver Click on Tools

  16. Using Solver • invoke Solver Click on Solver

  17. Using Solver • invoke Solver

  18. 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

  19. Check the Value of: circle Using Solver • complete the Solver Parameters dialog box

  20. 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)

  21. 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)

  22. Click on the By Changing Cells: box Using Solver • complete the Solver Parameters dialog box

  23. 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)

  24. 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)

  25. 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)

  26. 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)

  27. 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)

  28. 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)

  29. Click on Options Using Solver • set the Options

  30. 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)

  31. 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)

  32. Click on Solve Using Solver • execute Solver

  33. 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.

  34. Click on OK Using Solver • end execution

  35. 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

More Related