GOALSEEK Part of Standard Excel Installation Finds the
- Slides: 30
GOALSEEK Part of Standard Excel Installation Finds the root of a scalar function • 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 Make use of named ranges in Excel Create solution variable cell “xval” and target cell “fofx”
Highlight cells that will be named. Under Formulas Tab select Create from Selection
GOALSEEK - EXAMPLE Input the function into the “target cell” Use the named variable Type “=xval – cos(xval)” into the target cell
EXAMPLE 1 - SOLUTION
GOALSEEK - EXAMPLE Put an initial guess in the variable cell 0. 5 is the middle of the interval, so use it Now invoke the GOALSEEK function You can use the named ranges in the GOALSEEK dialog box, or point and click
GOALSEEK Goal Seek found on Data Tab under “What-If Analysis”
EXAMPLE 1 - SOLUTION
GOALSEEK - EXAMPLE Then click “OK”
EXAMPLE 1 - SOLUTION
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 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)
FINDING THE ADD-INS IN EXCEL 2000 Go to Excel Options
FINDING THE ADD-INS IN EXCEL Go to Add-Ins Tab on left and click Go at bottom
FINDING THE ADD-INS IN EXCEL Make sure Solver Add-In is checked and click ok
FINDING THE SOLVER ADD-IN Now go to Data tab and click on Solver on the far right
SOLVER - EXAMPLE Find the minimum of f(x, y)=(x-5)2 + (y-5)2
SOLVER - EXAMPLE Use named ranges to establish variables for ‘xval’ and ‘yval’ Use named range to create a target cell ‘fxy’
EXAMPLE 2: CREATING NAMED RANGES
SOLVER - EXAMPLE 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”)
EXAMPLE 2: SETTING UP THE SPREADSHEET
SOLVER - EXAMPLE Invoke the SOLVER function
MICROSOFT HELP ON “SOLVER” Define and solve a problem by using Solver 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.
EXAMPLE 2: INVOKING THE SOLVER FUNCTION Solver is found on far right of Data tab
MICROSOFT HELP ON “SOLVER” � 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.
EXAMPLE 2: THE SOLVER DIALOG BOX
MICROSOFT HELP ON “SOLVER” 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 To Solve. keep the solution values on the worksheet, click Keep Solver Solution in the Solver Results dialog box.
EXAMPLE 2: THE SOLVER SOLUTION
MICROSOFT HELP ON “SOLVER” To restore the original data, click Restore Original Values. Tips You can interrupt the solution process by pressing ESC. Microsoft Excel recalculates the worksheet with the last values found for the adjustable cells.
- He who finds a wife
- Standard installation unifi
- Meghadūta
- Liam finds a story
- Laputa gulliver's travels
- Whoever loses his life will find it
- An is software that finds websites webpages images
- What is the definition of personal responsibility
- Length iy
- Compiler lecture
- Loanable funds graph
- An is software that finds websites webpages images
- Fast finds
- Standard error excel regression
- Standard deviation of return
- Std dev symbol
- Distribution table
- Hát kết hợp bộ gõ cơ thể
- Slidetodoc
- Bổ thể
- Tỉ lệ cơ thể trẻ em
- Chó sói
- Glasgow thang điểm
- Chúa yêu trần thế alleluia
- Các môn thể thao bắt đầu bằng tiếng chạy
- Thế nào là hệ số cao nhất
- Các châu lục và đại dương trên thế giới
- Công thức tính độ biến thiên đông lượng
- Trời xanh đây là của chúng ta thể thơ
- Mật thư anh em như thể tay chân
- Làm thế nào để 102-1=99