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

Loading in 2 Seconds...

play fullscreen
1 / 35

Solving Equations with Excel Solver for Microsoft Excel 2000 - PowerPoint PPT Presentation


  • 1634 Views
  • Uploaded on

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.

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 Excel Solver for Microsoft Excel 2000' - MikeCarlo


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

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.

preparing the worksheet for solver
Preparing the Worksheet for Solver
  • start with a blank sheet
preparing the worksheet for solver5
Preparing the Worksheet for Solver
  • enter labels and constants

Enter labels in cells A1:A13 and B1

preparing the worksheet for solver6
Preparing the Worksheet for Solver
  • enter labels and constants

NOTE: These labels are not essential for the use of Solver

preparing the worksheet for solver7

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

preparing the worksheet for solver8

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

preparing the worksheet for solver9

Remember…

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

10

Preparing the Worksheet for Solver
  • enter formula

Click on cell B13

preparing the worksheet for solver10

Remember…

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

10

Preparing the Worksheet for Solver
  • enter formula

Type in =AVERAGE(

preparing the worksheet for solver11

Remember…

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

10

Preparing the Worksheet for Solver
  • enter formula

Highlight cells B2:B11

preparing the worksheet for solver12

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

preparing the worksheet for solver13
Preparing the Worksheet for Solver
  • enter formula

NOTE: Alternative formula for cell B13: =(B2+ B3+ B4+ B5+ B6+ B7+ B8+ B9+ B10+ B11)/10

preparing the worksheet for solver14
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

using solver
Using Solver
  • invoke Solver

Click on Tools

using solver16
Using Solver
  • invoke Solver

Click on Solver

using solver17
Using Solver
  • invoke Solver
using solver18

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

using solver19

Check the Value of: circle

Using Solver
  • complete the Solver Parameters dialog box
using solver20

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)

using solver21
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)

using solver22

Click on the By Changing Cells: box

Using Solver
  • complete the Solver Parameters dialog box
using solver23
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)

using solver24

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)

using solver25

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)

using solver26

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)

using solver27

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)

using solver28

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)

using solver30

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)

using solver31

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)

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

using solver35

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