Microsoft Excel is a spreadsheet application. One of its most significant advantages is the ability to perform various calculations using built-in formulas and functions.
Necessary
MS Excel
Instructions
Step 1
Solve a nonlinear equation in Excel using the example of the following task. Find the roots of the polynomial x3 - 0, 01x2 - 0, 7044x + 0, 139104 = 0. To do this, first solve the equation graphically. It is known that to solve such an equation, it is necessary to find the point of intersection of the graph of the function f (x) and the abscissa axis, that is, it is necessary to find out such a value of x at which the function will vanish.
Step 2
Tabulate the polynomial on the interval, for example, from –1 to 1, take step 0, 2. Enter –1 in the first cell, –0, 8 in the next, then select both, move the mouse cursor over the lower right corner to the plus sign appears, and drag until the value 1 appears.
Step 3
Then, in the cell to the right of –1, enter the formula = A2 ^ 3 - 0.01 * A2 ^ 2 - 0.7044 * A2 + 0.19104. Use AutoComplete to find y for all x values. Plot the function from the calculated results. On the graph, find the intersections of the abscissa and determine the intervals at which the roots of the polynomial are located. In our case, these are [-1, -0.8] and [0.2, 0.4], as well as [0.6, 0.8].
Step 4
Find the roots of the equation using successive approximation. Set the error in calculating the roots, as well as the limit number using the "Tools" menu and the "Options" tab. Enter the initial approximations and values of the function, then call the "Service" menu, the "Parameter selection" item.
Step 5
Fill in the dialog box that appears as follows: in the "Set in cell" field, enter B14 (reference to the cell that is assigned to the desired variable), in the "Value" field, set 0 (the right side of the equation), and in the "Changing the cell value" field, enter an absolute reference to cell A14 (the cell with the formula used to calculate the value of the left half of the equation). It is more convenient to enter links not manually, but by selecting the necessary cells with the left mouse button. Click OK. The selection result will be displayed on the screen. Search for the two remaining roots in the same way.
