Finding roots using Excel sheet
Short Summary:
This video tutorial demonstrates how to find roots of equations and solve systems of equations using Microsoft Excel's Goal Seek and Solver tools. Key points covered include using Goal Seek to iteratively find solutions by setting a target cell value to zero, and using Solver to solve systems of equations by setting multiple target cells to zero simultaneously. The tutorial uses polynomial equations (including cubic and binomial equations) and a system of linear equations as examples. The applications extend to finding maximum and minimum values of functions. Detailed step-by-step processes for using both Goal Seek and Solver are provided, including explanations of how to add the Solver add-in to Excel.
Detailed Summary:
The video begins with a recap of previous weeks' CM lab sessions, covering Excel basics, plotting, matrix manipulation, and numerical integration/differentiation. The instructor then introduces the main topic: finding roots of equations and solving systems of equations using Excel's Goal Seek and Solver tools.
Section 1: Introduction to Goal Seek and Solver
The instructor explains that Goal Seek is used for finding roots of equations (by setting a function equal to zero and iteratively adjusting an independent variable), while Solver is used for solving systems of equations and finding maximum/minimum values of functions. The instructor emphasizes the need to add the Solver add-in to Excel if it's not already present.
Section 2: Goal Seek Method - Detailed Explanation and Examples
This section provides a step-by-step guide to using Goal Seek. The instructor demonstrates this with several examples:
- Example 1: Solving a simple equation,
e^(-x) - sin(x) = 0, within a specified range (0 to 1). The process involves choosing an initial guess for 'x', calculating the corresponding 'y' value using the equation, and then using Goal Seek to find the 'x' value that makes 'y' equal to zero. The instructor highlights the importance of the "$" symbol in cell references to maintain cell addresses when copying formulas. - Example 2: Solving a cubic equation,
3x³ - 15x² - 20x + 50 = 0. The instructor first plots the function to visually estimate the roots, then uses Goal Seek to find the precise values of the roots. - Example 3: Solving a system of two equations by combining them into a single equation and applying Goal Seek.
Section 3: Solver Method - Detailed Explanation and Examples
This section focuses on using Solver. The instructor explains how to add the Solver add-in if necessary. The main example involves solving a system of three linear equations. The process includes:
- Setting up the equations in Excel cells, with the constants moved to the left-hand side to set the equations to zero.
- Using the Solver tool, setting the objective to make the results of the equations equal to zero by changing the values of the variables (x, y, z).
- The instructor emphasizes the importance of unchecking the "Make Unconstrained Variables Non-Negative" option when solving systems of equations.
Section 4: Solver Method for Finding Maximum and Minimum Values
The instructor demonstrates how to use Solver to find the maximum and minimum values of a function (sin(u) + cos(u)). This involves plotting the function to visually identify approximate locations of maxima and minima, then using Solver to find the precise values and corresponding angles.
Section 5: Conclusion and Assignment
The instructor summarizes the techniques covered and assigns homework involving applying both Goal Seek and Solver to solve similar problems. The instructor states, "today we have learned two different Tools in Excel one is goal method and another was the solver method," highlighting the key takeaway of the session.