Tackling Equations with Excel Solver for Microsoft Excel 2000 .


39 views
Uploaded on:
Category: Home / Real Estate
Description
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.
Transcripts
Slide 1

Illuminating Equations with Excel Solver for Microsoft Excel 2000

Slide 2

A Simple Problem grades, grades, grades*… You would love to have a test normal of 80 focuses (or better) in the QTM1300 course before the end of the semester. Your test scores on the initial eight tests are given in the table beneath. What scores do you have to acquire on the 9 th and 10 th last tests with a specific end goal to meet your objective normal of 80 points? * from FCL Excel Notes by Joyce Gordon, Babson College, 1998.

Slide 3

Quiz Average 90 + 88 + 78 + 68 + 84 + 82 + 66 + 74 + x + y = 80 10 Formulate the Problem get condition to be illuminated You are searching for x = score on 9 th quiz (0  x 100) y = score on 10 th quiz (0  y 100) so that NOTE: When utilizing Excel Solver to settle a condition ensure the right-hand side is a consistent.

Slide 4

Preparing the Worksheet for Solver begin with a clear sheet

Slide 5

Preparing the Worksheet for Solver enter marks and constants Enter names in cells A1:A13 and B1

Slide 6

Preparing the Worksheet for Solver enter names and constants NOTE: These names are not key for the utilization of Solver

Slide 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 names and constants Enter constants in cells B2:B9

Slide 8

Remember… 90 + 88 + 78 + 68 + 84 + 82 + 66 + 74 + x + y = 80 10 x y Preparing the Worksheet for Solver enter recipe Variable values in cells B10 and B11 Left-hand side of condition in cell B13 NOTE: These phones will be hued to demonstrate that they are fundamental for Solver

Slide 9

Remember… 90 + 88 + 78 + 68 + 84 + 82 + 66 + 74 + x + y = 80 10 Preparing the Worksheet for Solver enter equation Click on cell B13

Slide 10

Remember… 90 + 88 + 78 + 68 + 84 + 82 + 66 + 74 + x + y = 80 10 Preparing the Worksheet for Solver enter equation Type in =AVERAGE(

Slide 11

Remember… 90 + 88 + 78 + 68 + 84 + 82 + 66 + 74 + x + y = 80 10 Preparing the Worksheet for Solver enter equation Highlight cells B2:B11

Slide 12

Remember… 90 + 88 + 78 + 68 + 84 + 82 + 66 + 74 + x + y = 80 10 Preparing the Worksheet for Solver enter recipe Type in ) and hit Enter

Slide 13

Preparing the Worksheet for Solver enter equation NOTE: Alternative recipe for cell B13 : =( B2 + B3 + B4 + B5 + B6 + B7 + B8 + B9 + B10 + B11 )/10

Slide 14

Preparing the Worksheet for Solver enter recipe NOTE: The capacity AVERAGE entered in cell B13 gives back the normal of the qualities in cells B2:B11 ; clear cells are overlooked

Slide 15

Using Solver summon Solver Click on Tools

Slide 16

Using Solver conjure Solver Click on Solver

Slide 17

Using Solver summon Solver

Slide 18

NOTE: The phone showed in the Set Target Cell: box must contain the equation for the left-hand side of the condition (cell B13 ) Using Solver finish the Solver Parameters exchange box Click on cell B13

Slide 19

Check the Value of: circle Using Solver finish the Solver Parameters discourse box

Slide 20

Click on the Value of: box Using Solver finish the Solver Parameters exchange box NOTE: The esteem showed in the Value of: box must be the steady on the right-hand side of the condition ( 80 )

Slide 21

Using Solver finish the Solver Parameters exchange confine Type 80 NOTE: The esteem showed in the Value of: box must be the consistent on the right-hand side of the condition ( 80 )

Slide 22

Click on the By Changing Cells: box Using Solver finish the Solver Parameters discourse box

Slide 23

Using Solver finish the Solver Parameters discourse box Highlight cells B10:B11 NOTE: The phones showed in the By Changing Cells: box must be the phones containing variable qualities (cells B10:B11 )

Slide 24

Click on Add Using Solver finish the Solver Parameters exchange box NOTE: The Subject to the Constraints: box must contain the requirements on the variable qualities ( x , y  100)

Slide 25

Click on the Cell Reference: box Using Solver finish the Solver Parameters exchange box Highlight cells B10:B11 NOTE: The Subject to the Constraints: box must contain the imperatives on the variable qualities ( x , y  100)

Slide 26

Make beyond any doubt <= is shown Using Solver finish the Solver Parameters exchange box NOTE: The Subject to the Constraints: box must contain the limitations on the variable qualities ( x , y  100)

Slide 27

Click on the Constraint: box and sort in 100 Using Solver finish the Solver Parameters discourse box NOTE: The Subject to the Constraints: box must contain the requirements on the variable qualities ( x , y  100)

Slide 28

Click on OK Using Solver finish the Solver Parameters exchange box NOTE: The Subject to the Constraints: box must contain the limitations on the variable qualities ( x , y  100)

Slide 29

Click on Options Using Solver set the Options

Slide 30

Check the Assume Linear Model and the Assume Non-Negative boxes Using Solver set the Options NOTE: The condition (in the Target Cell B13 ) is straight on the non-negative factors ( x in B10 and y in B11 )

Slide 31

Accept the rest of the default choices by tapping on OK Using Solver set the Options NOTE: The condition (in the Target Cell B13 ) is direct on the non-negative factors ( x in B10 and y in B11 )

Slide 32

Click on Solve Using Solver execute Solver

Slide 33

Using Solver read arrangement You have to gain a score of 100 on the 9 th test and 70 on the 10 th test to meet your objective normal of 80 focuses.

Slide 34

Click on OK Using Solver end execution

Slide 35

You require variable based math! Utilizing Solver a last remark COMPLETE SOLUTION You have to win a score of x on the 9 th test, where 70  x  100, and 170 – x on the 10 th test to meet your objective normal of 80 focuses. NOTE: Excel Solver won\'t not locate every conceivable arrangement

Recommended
View more...