Index

Tips and Troubleshooting

• Arranging equations in optimal form
• Tips for using Excel when entering equations
  Shortcut for copying cells; using cell names; moving formulas vs. copying; Excel's order of operation
• Picking first-estimates of solutions
• Troubleshooting
  Entry mistakes; derivative errors; math errors; difficulty finding all polynomial roots; divergence to infinity; oscillating approximations instead of convergence
• How to tile windows to view Procedures and Sample Spreadsheets simultaneously

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:

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:

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:

Optimal	Non-Optimal
F(z) = 7 - 2z 3 - 13z 5	F(z) = 7z -5 - 2z -2 - 13
limit of |F(z)| as z --> + infinity = infinity	limit of |F(z)| as z --> + infinity = 13
limit of |F(z)| as z --> (any finite number) = finite number	limit of |F(z)| as z --> 0 = infinity

Non-optimal equations can be converted to optimal equations by two methods:

1. Multiply both sides of F(w) = 0 by a function of w that yields an optimal equation. Solve for w, and test each solution to verify that it is a root of the original equation.
   Example: Multiply 7w^-5 - 2w^-2 - 13 = 0 by w⁵ to get 7 - 2w³ - 13 w⁵ = 0, which is optimal.
    
2. Substitute w = 1/q to get a new equation, F(q) = 0, whose terms have positive exponents. Solve for q, and then invert q to find w.
   Example: Substitute w = 1/q in 7w^-5 - 2 w^-2 - 13 = 0 to get 7q⁵ - 2 q² - 13 = 0, which is optimal. Compute the five solutions of q, and invert each to get a solution of w.

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).

1. Drag the cursor over the block of adjacent cells to highlight them.
2. Place the cursor over the lower-right corner of the block. The cursor will change from a white cross to a black cross.
3. Left-click the mouse and drag down the column (or up the column). The cellblock is pasted when you release the mouse-button.
4. If you right-click the mouse instead of left-clicking, a menu pops up when the mouse-button is released. This menu offers several options for copying.
5. A block of cells in a column can be copied left or right by following the steps 1 and 2, and then dragging left or right for step 3.

To copy a whole row of cells downward:

1. Click on the Row Number (in the left-margin of the worksheet) to highlight the row.
2. Place the cursor over the lower-right corner of the Row Number (the cursor should change to a black cross).
3. Left-click the mouse and drag down the column. The row is pasted when you release the mouse-button.
4. A whole column can be copied in similar fashion.

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.

• Cell names must begin with a letter, not a number.
• Spaces are not allowed in a name. The underscore may be used instead of a space.
• Some single letters can be used as names, but not all (the letter r is not allowed as a name) .
• Letter-number combinations that look like cell references, such as J2, are not allowed as names.
• Words reserved for functions, such as COS, are not valid names.
• Most punctuation marks (except the underscore) are not allowed in 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.

• If you highlight cell A6 and MOVE the contents to cell D10, then the formula in cell D10 will still refer to cell B5.
• If you highlight cell A6 and COPY the contents to cell D10, then the formula in cell D10 will refer to cell E9, because cell E9 is positioned one row above, and one column to the right, of cell D10.

4. Excel's order of operation when negative signs are involved

In algebra, the expressions F(z) = -z² and F(z) = -1*z² both mean F(z) = -(z²), not F(z) = (-z)². 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.

• If the range of solutions is predictable, then pick a first estimate within range. An example is a financial equation where the solution is an annuallized interest rate. Interest rates are positive numbers between 0 and 1, and a first estimate of 10% might be a reasonable choice.
• If the solutions' range is not predictable, then pick simple numbers both large and small, positive and negative. I usually start with z = 1, and then try z = 0, -1, 100, -100, and see what happens. For complex estimates, I usually start with (x,y) = (1,1), and then try (1,-1), (0,1), (0,-1), and similar variations.
• The complex number "zero" is defined in linear coordinates (x + iy = 0 + 0i), but not in polar coordinates (imaginary argument q is undefined when R=0). Therefore, do not pick (x,y) = (0,0) as a first estimate.
• If F(z) is a polynomial with real coefficients and exponents, then every complex root computed is really worth two roots, because the complex-conjugate will also be a root. This can be verified by picking the complex-conjugate of the first estimate that yielded a root.
• A division-by-zero error occurs if slope S(z) equals zero, or if F(z) has negative exponents and the approximations approach zero. The remedy is to pick a different first estimate.

Troubleshooting

1. Entry Mistakes

Here is a collection of mistakes that I've made while entering functions into a spreadsheet:

• Typing functions manually (and incorrectly) when I could have copied and pasted them.
• Pasting the functions Fr, Fi, Sr and Si into the wrong columns.
• Moving active functions from one cell to another by pointing and dragging, when I should have copied and pasted them (the cell references in the function do not change when a function is moved).
• Copying and pasting active functions, when I should have moved them instead.
• Forgetting to delete the real constants from the imaginary functions Fi and Si.
• Correcting a mistake in one cell, but then forgetting to paste the correction over the erroneous cells below it.
• Omitting asterisks to indicate multiplication (this usually provokes an error-message from Excel).
• Failing to match opening and closing parentheses (this always provokes an error-message from Excel).
• Staying-up past my bedtime on a weeknight trying to troubleshoot successive approximation problems.

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 (w - root). If the polynomial has N degenerate roots, each represented as g, then the polynomial will include the factor (w - g)^N. Similarly, the unrestricted function F(z) will include the factor (z - g)^N. Notice that the derivative of F(z) will include the factor (z - g)^N-1, and so g must be a root of S(z) too.

If a lack of solutions cannot be explained by degeneracy, then divide the polynomial by the product of all known factors, (w - root). Be forewarned that this can test your determination to find all the roots. Then solve the remainder polynomial for the rest of the roots.

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:

1. There are no real solutions to the equation; all of the solutions are complex. If this is the case, then the approximations typically jump around from positive to negative, in apparently random directions. The remedy is to try using the complex approximation functions.
2. There are real solutions to the equation, but F(z) has a minimum above the z-axis (or a maximum below it) and the approximations are getting "trapped" in the minimum. To get a picture of how this might happen, view this Graphic Representation of the Real-Valued Approximation Function, and imagine a minimum between w and z. If your approximations are trapped, then they will oscillate around the positive-valued minimum or negative-valued maximum. The remedy is to pick different first-estimates to avoid sliding into a minimum or maximum.

How to tile windows to view Procedures and Sample Spreadsheets simultaneously

To tile Microsoft windows:

1. Minimize any windows that you don't need to view.
2. Place your cursor over the horizontal toolbar at the bottom of your display.
3. Right-click the mouse (a menu should pop-up)
4. Select "Tile Windows Horizontally."
5. Adjust the sizes and positions of the windows as you wish.
6. If unwanted windows appeared among the tiles, then minimize them and repeat from step 2.

Index

URL http://members.ispwest.com/patrussell/approximations/Tips.htm
Unpublished Work. © Copyright 2001 Pat Russell. Updated April 27, 2003.