Solving Equations with Goal Seek for Microsoft Excel 2000

1 / 26

# Solving Equations with Goal Seek for Microsoft Excel 2000 - PowerPoint PPT Presentation

Solving Equations with Goal Seek 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.

I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.

## Solving Equations with Goal Seek for Microsoft Excel 2000

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.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.

- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript

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

A Simple Problem
• 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
• 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

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