Arranging equations in optimal form, F(w) = 0.
An equation can be processed into new equations by operating on both sides of the equal sign. The old equation and new equations are a family whose members are different expressions describing the same relationship. For example, the following two equations describe the same relationship:
7 w -5 - 2 w -2 - 13 = 0
Each of these equations can be manipulated to yield the other equation. However, the two equations are not equally suitable for solving by successive approximations. Using the first equation, successive approximations would converge on solutions, but using the second equation, the approximations could diverge to infinity.
An equation F(w) = 0 is optimal for successive approximations if the corresponding function, F(z), satisfies two requirements:
1. The absolute value of F(z) approaches infinity as z approaches plus infinity and minus infinity:
2. The absolute value of F(z) is finite as z approaches any number, n, between -infinity and +infinity:
An equation is non-optimal for successive approximations if |F(z)| approaches finite limits as z approaches plus or minus infinity, and if |F(z)| approaches infinity as z approaches some finite number n:
limit of |F(z)| as z
Optimal equations typically have at least one term with a positive exponent, while non-optimal equations only have terms with negative or zero-valued exponents. This is evident in the above example of two related equations. The limits of their corresponding functions as z becomes infinite verifies which equation is optimal and which is not:
|F(z) = 7 - 2z 3 - 13z 5||F(z) = 7z -5 - 2z -2 - 13|
|limit of |F(z)| as z ||limit of |F(z)| as z |
|limit of |F(z)| as z ||limit of |F(z)| as z |
Non-optimal equations can be converted to optimal equations by two methods:
Sometimes |F(z)| approaches infinity as z increases in one direction (positive or negative), but then |F(z)| approaches a limit as z increases in the other direction. For these functions, determine the direction of increasing z for which |F(z)| approaches a limit, and be aware that successive approximations could diverge if z moves too far in that direction. Then pick first estimates of w that are large in the direction that z approaches infinity. It might be useful to create a graph of F(z) in your spreadsheet to see where F(z) crosses the z-axis, and then pick first-estimates near that point (be aware that some functions have a graph for positive z-values, and another graph for negative z-values, and maybe only one graph crosses the z-axis).
If F(z) is a periodic function, then it might not approach infinity in any direction (there could also be infinite solutions to the original equation). However, successive approximation can still converge on solutions within a limited range of interest.
Be aware that equations need not be simplified as they would for homework problems or exams. It is unneccessary to collect all common terms, factor common multiples, or multiply terms in parentheses. Because each operation performed carries the risk of error, I recommend only simplifying enough to make F(z) easy to differentiate.
Tips for entering functions in an Excel spreadsheet
1. Shortcut for copying a block of cells in one row to the rows below (or above).
To copy a whole row of cells downward:
2. Using absolute cell references and cell names as parameters.
Suppose that your equation contains parameters, and you want to solve the equation with different values of these parameters. An example is an investment calculation involving a discount rate, annual return, and number of years, all of which you might want to vary to compare different conditions.
Instead of using numbers for the parameters in the spreadsheet-functions, you could enter the parameters in their own cells and then use their Absolute Cell References in the functions. Unlike normal cell references, absolute cell references do not change when copied and pasted. If you entered a discount-rate value in cell B3, then its absolute cell reference is $B$3, which you could use as the discount-rate parameter in your functions.
A problem with absolute cell references is that they are not easy to memorize. When entering absolute references in a function, you would probably have to look around the worksheet to find the parameter and its cell reference. An alternative to using absolute cell references is using cell names, which are easier to remember. Excel has a Name Box that is usually located above cell A1 and directly below the Font Box (the Name Box displays the cell reference of whatever cell is highlighted). If cell B3 contains an interest-rate parameter, you can name cell B3 "rate" by highlighting it and then typing "rate" in the Name Box. You could then use the name "rate" in your functions as a parameter. If you want to change the parameter later on, you would simply type a new number in cell B3.
Here are some Excel rules about allowed cell names.
3. How MOVING a formula to another cell differs from COPYING the formula to another cell.
Suppose that cell A6 contains a formula that refers to cell B5. Notice that cell B5 is positioned one row above, and one column to the right, of cell A6.
4. Excel's order of operation when negative signs are involved
In algebra, the expressions F(z) = -z2 and F(z) = -1*z2 both mean F(z) = -(z2), not F(z) = (-z)2. But in Excel, the expression "= -A3^2" means "= (-A3)^2", while the expression "= -1*A3^2" means "= -(A3^2)". The same interpretations occur if cell-reference A3 is replaced by an actual number or by a function.
Therefore, if your equation has negative coeficients, use parentheses liberally to instuct your spreadsheet to use your preferred order of operations.
Picking first estimates of z.
1. Entry Mistakes
Here is a collection of mistakes that I've made while entering functions into a spreadsheet:
2. Derivative Errors
If a mistake occurs while finding or entering the slope S(z), then the approximations might still converge on correct solutions to F(z), provided the error in S(z) is small. If the error in S(z) is large, then the approximations will oscillate around correct solutions instead of converging. The same is true for complex approximations if Sr and Si have errors.
3. Math Errors made while manipulating non-optimal equations into optimal form.
If the original equation was not in optimal form, and if a mistake was made while manipulating the equation into optimal form, then the approximations will converge on solutions, but they will be wrong solutions. Always double-check solutions in the original non-optimal equations.
4. Difficulty finding all polynomial roots.
An Nth-order polynomial has N roots, generally complex, but sometimes only a few roots are found and the rest are evasive. When this happens, paste each known solution into the first-estimate cells. If S(w) returns a zero (or if Sr and Si are both zero), then the solution represents two or more roots of F(w) = 0. When two or more roots are equal, they are called "degenerate" roots.
To understand why S(w) = 0 if w is degenerate, remember that a polynomial can be written as the product of factors
If a lack of solutions cannot be explained by degeneracy, then divide the polynomial by the product of all known factors,
5. Divergence to Infinity
Approximations will diverge to infinity if function F(z) approaches a limit as z approaches infinity in one direction or another. The slope of F(z) guides the approximations toward the z-axis, but if F(z) levels-out parallel to the z-axis as z becomes infinite, then S(z) pushes the approximations toward infinity. This is why equations must be optimized before attempting successive approximations.
An infinite-divergence can occur when solving for real solutions and F(z) has no real solutions. The first corrective response when solving for real solutions is to look for complex solutions, too.
The second corrective response is to pick large first estimates in the direction that F(z) approaches infinity as z approaches infinity. At these large first estimates, S(z) guides the approximations toward the z-axis where the solution lies.
6. Lack of Convergence, characterized by oscillating approximations
When using the real-valued approximation functions, sometimes the approximations will not converge, nor will they diverge to infinity. There are two common causes of this:
How to tile windows to view Procedures and Sample Spreadsheets simultaneously
To tile Microsoft windows:
Unpublished Work. © Copyright 2001 Pat Russell. Updated April 17, 2009.