Solving Equations with Goal Seek for Microsoft Excel 2000

222 Views

Download Presentation
## Solving Equations with Goal Seek for Microsoft Excel 2000

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

**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 nine quizzes are given in the table below. • What score do you need to earn on the 10th and final quiz 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 + 72 + x = 80 10 Formulate the Problem • obtain equation to be solved You are looking for x = score on 10th quiz so that NOTE: Only use Goal Seek if the equation • contains only one variable; • has the right-hand side equal to a constant.**Preparing the Worksheet for Goal Seek**• start with a blank sheet**Preparing the Worksheet for Goal Seek**• enter labels and constants Enter labels in cells A1:A13 and B1**Preparing the Worksheet for Goal Seek**• enter labels and constants NOTE: These labels are not essential for the use of Goal Seek**Remember…**• Quiz • 1 • 2 • 3 • 4 • 5 • 6 • 7 • 8 • 9 • Score • 90 • 88 • 78 • 68 • 84 • 82 • 66 • 74 • 72 Preparing the Worksheet for Goal Seek • enter labels and constants Enter constants in cells B2:B10**Remember…**90 + 88 + 78 + 68 + 84 + 82 + 66 + 74 + 72 + x = 80 10 x Preparing the Worksheet for Goal Seek • enter formula Variable values in cell B11 Left-hand side of equation in cell B13 NOTE: These cells will be colored to indicate that they are essential for Goal Seek**Remember…**90 + 88 + 78 + 68 + 84 + 82 + 66 + 74 + 72 + x = 80 10 Preparing the Worksheet for Goal Seek • enter formula Click on cell B13**Remember…**90 + 88 + 78 + 68 + 84 + 82 + 66 + 74 + 72 + x = 80 10 Preparing the Worksheet for Goal Seek • enter formula Type in =AVERAGE(**Remember…**90 + 88 + 78 + 68 + 84 + 82 + 66 + 74 + 72 + x = 80 10 Preparing the Worksheet for Goal Seek • enter formula Highlight cells B2:B11**Remember…**90 + 88 + 78 + 68 + 84 + 82 + 66 + 74 + 72 + x = 80 10 Preparing the Worksheet for Goal Seek • enter formula Type in ) and hit Enter**Preparing the Worksheet for Goal Seek**• enter formula NOTE: Alternative formula for cell B13: =(B2+ B3+ B4+ B5+ B6+ B7+ B8+ B9+ B10+ B11)/10**Preparing the Worksheet for Goal Seek**• 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 Goal Seek**• invoke Goal Seek Click on Tools**Using Goal Seek**• invoke Goal Seek Click on Goal Seek**Using Goal Seek**• invoke Goal Seek**NOTE: The cell displayed in the Set cell: box must contain**the formula for the left-hand side of the equation (cell B13) Using Goal Seek • complete the Goal Seek dialog box Click on cell B13**Using Goal Seek**• complete the Goal Seek dialog box Click on the To value: box**Using Goal Seek**• complete the Goal Seek dialog box Type in 80 NOTE: The value displayed in the To value: box must be the constant on the right-hand side of the equation (80)**Using Goal Seek**• complete the Goal Seek dialog box Click on the By changing cell: box**Using Goal Seek**• complete the Goal Seek dialog box Click on cell B11 NOTE: The cell displayed in the By changing cell: box must be the cell containing variable values (cell B11)**Using Goal Seek**• execute Goal Seek Click on OK**Using Goal Seek**• read solution You need to earn a score of 98 on the 10th quiz to meet your goal average of 80 points. NOTE: If the equation has multiple solutions, Goal Seek usually finds the closest solution to the initial variable value.**Back to studying!**Using Goal Seek • end execution Click on OK**Final Comments**• Goal Seek might not find the exact solution… • Goal Seek has a precision of 0.001: • when the variable value returned by Goal Seek is plugged into the left-hand side of the equation, the result will be within 0.001 of the right-hand side; • use Excel Solver for higher precision and/or equations with multiple variables.