Goalseek
Download
1 / 30

GOALSEEK - PowerPoint PPT Presentation


  • 91 Views
  • Uploaded on

Part of Standard Excel Installation Finds the root of a scalar function. GOALSEEK. Recall that root-finding methods (e.g., bisection method, Newton’s method) rely on a change in sign of the objective function Any f(x) function used in GOALSEEK should have a well defined sign change.

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 ' GOALSEEK' - ashely-stanley


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
Goalseek

Part of Standard Excel Installation

Finds the root of a scalar function

GOALSEEK

  • Recall that root-finding methods (e.g., bisection method, Newton’s method) rely on a change in sign of the objective function

  • Any f(x) function used in GOALSEEK should have a well defined sign change


Goalseek example

Use Excel’s GOALSEEK feature to find the root of f(x) = x – cos(x) between 0 and 1.

GOALSEEK - Example


Goalseek example1

GOALSEEK - Example


Goalseek

Highlight cells that will be named. Under Formulas Tab select Create from Selection


Goalseek example2

GOALSEEK - Example



Goalseek example3

GOALSEEK - Example


Goalseek1
GOALSEEK

Goal Seek found on Data Tab under “What-If Analysis”



Goalseek example4

Then click “OK”

GOALSEEK - Example



Solver

Solver is an “add-in” to Excel

It is not installed by default

SOLVER will find a particular value, the maximum, or the minimum of a scalar function of a vector

SOLVER


Solver1

Uses a gradient-based method (like Newton’s method) to find the root or the max/min

Works very well for functions that have a well-defined minimum (or maximum), like a quadratic form (a parabola)

SOLVER


Finding the add ins in excel 2000
Finding the Add-ins find the root or the max/minin Excel 2000

Go to Excel Options


Finding the add ins in excel
FINDING The add-ins in Excel find the root or the max/min

Go to Add-Ins Tab on left and click Go at bottom


Finding the add ins in excel1
Finding The Add-ins in Excel find the root or the max/min

Make sure Solver Add-In is checked and click ok


Finding the solver add in
Finding the SOLVER Add-in find the root or the max/min

Now go to Data tab and click on Solver on the far right


Solver example

Find the minimum of find the root or the max/min

SOLVER - Example

f(x,y)=(x-5)2 + (y-5)2


Solver example1

Use named ranges to establish variables for ‘xval’ and ‘yval’

Use named range to create a target cell ‘fxy’

SOLVER - Example



Solver example2

Put initial guesses for xval, yval in their cells (use (0,0))

Type the formula into the target cell (“=(xval-5)^2 + (yval-5)^2”)

SOLVER - Example



Solver example3

Invoke the SOLVER function (0,0))

SOLVER - Example


Microsoft help on solver

  • Define and solve a problem by using Solver (0,0))

    • On the Data Tab, click Solver (far right).

    • If the Solver command is not on the Data Tab, you need to install the Solver add-in.

    • In the Set Target Cell box, enter a cell reference or name for the target cell. The target cell must contain a formula.

MicroSoft Help on “SOLVER”


Example 2 invoking the solver function
Example 2: Invoking the SOLVER function (0,0))

Solver is found on far right of Data tab


Microsoft help on solver1

  • To have the value of the target cell be as large as possible, click Max.

  • To have the value of the target cell be as small as possible, click Min.

  • To have the target cell be a certain value, click Value of, and then type the value in the box.

  • In the By Changing Cells box, enter a name or reference for each adjustable cell, separating nonadjacent references with commas. The adjustable cells must be related directly or indirectly to the target cell. You can specify up to 200 adjustable cells.

MicroSoft Help on “SOLVER”



Microsoft help on solver2

  • To have Solver automatically propose the adjustable cells based on the target cell, click Guess.

  • In the Subject to the Constraints box, enter any constraints you want to apply.

  • Click Solve.

  • To keep the solution values on the worksheet, click Keep Solver Solution in the Solver Results dialog box.

MicroSoft Help on “SOLVER”


Example 2 the solver solution
Example 2: The SOLVER solution based on the target cell, click


Microsoft help on solver3

  • Tips

    • You can interrupt the solution process by pressing ESC. Microsoft Excel recalculates the worksheet with the last values found for the adjustable cells.

  • MicroSoft Help on “SOLVER”


    ad