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

Illuminating Equations with Excel Solver for Microsoft Excel 2000

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.

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.

Preparing the Worksheet for Solver begin with a clear sheet

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

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

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

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

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

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

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

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

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

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

Using Solver summon Solver Click on Tools

Using Solver conjure Solver Click on Solver

Using Solver summon Solver

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

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

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 )

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 )

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

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 )

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)

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)

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)

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)

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)

Click on Options Using Solver set the Options

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 )

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 )

Click on Solve Using Solver execute Solver

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.

Click on OK Using Solver end execution

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