Page 197 - Excel for Scientists and Engineers: Numerical Methods

174 EXCEL: NUMERICAL METHODS

The function has one real root and a pair of imaginary roots. Figure 8-30
shows a portion of the spreadsheet in which the Bairstow custom function is used
to obtain the roots of the function.

Figure 8-30. Calculation of all roots (real and imaginary) of a regular polynomial
by the Bairstow custom function.
(folder 'Chapter 08 Examples', workbook 'Bairstow', sheet 'Example 2')
The formula
=A2"3-0.0031 *A2"2+0.000000023*A2+0.000000005

was entered in cell B2 and the Bairstow custom function
{=Bairstow(B2,A2)}
in cells A27:B29. The real part of the root is in the left cell and the imaginary
part in the right cell. Note that, since the custom function handles only
polynomials with real coefficients, the complex roots (if any) occur in conjugate
pairs.

Finding Values Other than Zeroes

of a Function

Many of the preceding methods can be modified so as to find the x of a
function for a y value other than zero. In this way you can find, for example, the
point of intersection of two curves (the x value where the y value of one function
equals they value of another function). Some examples follow.

Using Goal Seek ...
to Find the Point of Intersection of Two Lines
It is a simple matter to use Goal Seek ... to find the intersection of two lines,
as illustrated in Figure 8-3 1