Solving Equations with Excel Solver for Microsoft Excel 2000

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