solving equations with goal seek for microsoft excel 2000 l.
Download
Skip this Video
Loading SlideShow in 5 Seconds..
Solving Equations with Goal Seek for Microsoft Excel 2000 PowerPoint Presentation
Download Presentation
Solving Equations with Goal Seek for Microsoft Excel 2000

Loading in 2 Seconds...

play fullscreen
1 / 26

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


  • 217 Views
  • Uploaded on

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.

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

PowerPoint Slideshow about 'Solving Equations with Goal Seek for Microsoft Excel 2000' - liam


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.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
a simple problem
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.
formulate the problem

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 seek5
Preparing the Worksheet for Goal Seek
  • enter labels and constants

Enter labels in cells A1:A13 and B1

preparing the worksheet for goal seek6
Preparing the Worksheet for Goal Seek
  • enter labels and constants

NOTE: These labels are not essential for the use of Goal Seek

preparing the worksheet for goal seek7

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

preparing the worksheet for goal seek8

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

preparing the worksheet for goal seek9

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

preparing the worksheet for goal seek10

Remember…

90 + 88 + 78 + 68 + 84 + 82 + 66 + 74 + 72 + x = 80

10

Preparing the Worksheet for Goal Seek
  • enter formula

Type in =AVERAGE(

preparing the worksheet for goal seek11

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

preparing the worksheet for goal seek12

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 seek13
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 seek14
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
Using Goal Seek
  • invoke Goal Seek

Click on Tools

using goal seek16
Using Goal Seek
  • invoke Goal Seek

Click on Goal Seek

using goal seek17
Using Goal Seek
  • invoke Goal Seek
using goal seek18

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 seek19
Using Goal Seek
  • complete the Goal Seek dialog box

Click on the To value: box

using goal seek20
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 seek21
Using Goal Seek
  • complete the Goal Seek dialog box

Click on the By changing cell: box

using goal seek22
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 seek23
Using Goal Seek
  • execute Goal Seek

Click on OK

using goal seek24
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.

using goal seek25

Back to studying!

Using Goal Seek
  • end execution

Click on OK

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