Index

Procedures for Using Successive Approximations in Your Spreadsheet

The procedures for computing real and complex solutions of one or more equations are shown in four tables on this page. Each procedure is broken down into steps, and each step includes an example calculation and a cell reference to sample spreadsheets. These sample sheets are shown on separate HTML files whose links appear in the top row of each procedure-table (except for Procedure 1, real solutions of one equation, whose sample sheet is shown below Procedure 1). If you have an advanced version of Excel, then you can copy rows of the sample sheets and paste them directly into an Excel file as active functions.

If your browser recognizes frames, then selecting the "Frames Version" of a procedure displays the procedure in the top frame, and the sample sheet in the bottom frame, so you can view simultaneously a step in the procedure and the corresponding Excel function. For all browsers, selecting the "No Frames Version" will display the procedure on this page without displaying the sample sheet. You can still view the procedure and sample sheet together by opening the sample sheet in a separate window, and then resizing the windows or tiling them horizontally. For Procedure 1, the procedure and sample sheet are so short that you can view them together without two windows.

Procedure 1. Finding Real Solutions of 1 Equation Frames Version No Frames Version
Procedure 2. Finding Complex Solutions of 1 Equation Frames Version No Frames Version
Procedure 3. Finding Real Solutions of Several Equations Frames Version No Frames Version
Procedure 4. Finding Complex Solutions of Several Equations Frames Version No Frames Version

Procedure 1. Finding Real Solutions of 1 Equation.

Steps (6 total) Example View Cells in Sheet 1A, 1B below this table
1. Write the equation in optimal form, F(w) = 0. w9 - 23w2 + 5w - 19 = 0 (not entered)
2. Let variable z be an approximation of solution w. Enter a value for z in your spreadsheet. First estimate: z = 1. Sheet 1A, Cell A2
3. Let F(z) be a function based on the equation. Compute F in the cell next to z. F(z) = z9 - 23z2 + 5z - 19 Sheet 1A, Cell B2
4. Let S(z) be the slope of F(z), S = dF/dz. Compute S in the cell next to F. S(z) = 9z8 - 46z + 5 Sheet 1A, Cell C2
5. Compute a new approximation with w ~ z - F(z)/S(z) Compute w in the cell below z. Sheet 1A, Cell A3
6. Repeat computations: copy all functions down their columns until z converges. Solution: w = 1.603611151 Sheet 1B, Cell A9

Sheet 1A. Excel functions that compute real solutions of w9 - 23w2 + 5w - 19 = 0
  A B C
1 Z F S
2 = 1 = A2^9 - 23*A2^2 + 5*A2 - 19 = 9*A2^8 - 46*A2 + 5
3 = A2 - B2/C2 = A3^9 - 23*A3^2 + 5*A3 - 19 = 9*A3^8 - 46*A3 + 5
4 = A3 - B3/C3 = A4^9 - 23*A4^2 + 5*A4 - 19 = 9*A4^8 - 46*A4 + 5

Sheet 1B. Values returned by functions in Sheet 1A.
  A B C
1 Z F S
2 1 -36 -32
3 -0.125 -19.984375 10.7500005
4 1.734011536 62.2457253 660.863668
5 1.639823085 13.0895851 400.131897
6 1.607109909 1.14824921 331.577204
7 1.603646917 0.01161858 324.884311
8 1.603611155 1.2263E-06 324.815732
9 1.603611151 0 324.815725
10 1.603611151 0 324.815725

 

Procedure 2. Finding Complex Solutions of 1 Equation.

Steps (7 total) Example View Cells in Sheet 2A, 2B
1. Write the equation in optimal form, F(w) = 0. w9 - 23w2 + 5w - 19 = 0 (not entered)
2. Let z = x + iy be an estimate of solution w = u + iv. Assign values to x and y, and enter them in a row of your spreadsheet. First estimate: x = 1, y = 1 Sheet 2A
A2:B2
3. Compute the absolute value R and imaginary argument q in the cells next to x and y R = (x2 + y2)1/2
q = +Arccos(x/R) if y > 0, q = -Arccos(x/R) if y < 0 		Sheet 2A
C2:D2
4. Let F(z) be a function based on the equation. Resolve F into real and imaginary parts, F = Fr + iFi. Compute Fr and Fi in the cells next to R and q. F(z) = z9 - 23z2 + 5z - 19 = Fr + iFi
Fr = R9Cos(9q) - 23R2Cos(2q) + 5RCos(q) -19
Fi = R9Sin(9q) - 23R2Sin(2q) + 5RSin(q) 		Sheet 2A
E2:F2
5. Let S(z) be the slope of F(z), S = dF/dz. Resolve S into real and imaginary parts, S = Sr + iSi. Compute Sr and Si in the cells next to Fr and Fi. S(z) = 9z8 - 46z + 5 = Sr + iSi
Sr = 9R8Cos(8q) - 46RCos(q) +5
Si = 9R8Sin(8q) - 46RSin(q) 		Sheet 2A
G2:H2
6. Compute a new approximation of w = u + iv with u ~ x - (Fr*Sr + Fi*Si)/(Sr2 + Si2) and v ~ y - (Fi*Sr - Fr*Si)/(Sr2 + Si2) Compute u and v in the cells below first estimate x and y. Use cell references in the approximation functions. Sheet 2A
A3:B3
7. Repeat computations: copy all functions down their columns until x and y converge. Solution: w = 1.00541938 + i1.16519460 Sheet 2B
A9:B9

 

Procedure 3. Finding Real Solutions of N Equations.

Steps (8 total) Example (2 Equations, 2 Variables) View Cells in Sheet 3A, 3B
1. Write the equations in optimal form, F1(w1, w2,...) = 0, F2(w1, w2,...) = 0, ... w13w25 - 6w1 w2 - 17 = 0
7w12w2 - 3w1 w23 + 5 = 0 		(not entered)
2. Let column-vector Z = [z1, z2,...] be an approximation of solution W = [w1, w2,...]. Assign values to Z for the first estimate. Enter Z as a column-vector in your spreadsheet. First estimate: Z = [1, 2]. Sheet 3A
A2:A3
3. Let column-vector F = [F1(Z), F2(Z),...] be a set of functions based on the equations. Compute F in the cells next to Z. F1(Z) = z13z25 - 6z1 z2 - 17
F2(Z) = 7z12z2 - 3z1 z23 + 5 		Sheet 3A
B2:B3
4. Let J be the Jacobian matrix whose elements are the partial derivatives of F. Compute J in the cells next to F. dF1/dz1 = 3z12z25 - 6z2, dF1/dz2 = 5z13z24 - 6z1
dF2/dz1 = 14z1 z2 - 3z23, dF2/dz2 = 7z12 - 9z1 z22 		Sheet 3A
C2:D3
5. Let matrix 1/J be the inverse of J. Compute 1/J in the cells next to J. In Excel, the element of 1/J in Row R, Column C is
= INDEX (MINVERSE (Matrix J), R, C) 		Sheet 3A
E2:F3
6. Let column-vector DZ be defined DZ = 1/J*F. Compute DZ in the cells next to 1/J. In Excel, the element of DZ in Row R is
= INDEX (MMULT (Matrix 1/J, Vector F), R, 1) 		Sheet 3A
G2:G3
7. Compute a new approximation using W ~ Z - DZ. Compute vector W in the cells below vector Z. Sheet 3A
A5:A6
8. Repeat computations: Copy the functions downward until Z converges and F approaches (0,0,...). Solution: W = [1.110552, 1.845165] Sheet 3B
A14:A15

 

Procedure 4. Finding Complex Solutions of N Equations.

Steps (15 total) Example (2 Equations, 2 Variables) View Cells In Sheet 4A, 4B
1. Write the equations in optimal form, F1(w1, w2,...) = 0, F2(w1, w2,...) = 0, ... w13w25 - 6w1 w2 - 17 = 0
7w12w2 - 3w1 w23 + 5 = 0 		not entered
2a. Let column-vector Z = [z1, z2,...] be an approximation of solution W = [w1, w2,...]. Assign values to Z as a first estimate. First estimate: Z = [1 - 2i, 1 - i] not entered
2b. Resolve Z into real and imaginary parts, Z = X + iY. Enter X and Y as adjacent column vectors in your spreadsheet. Z = [1, 1] + i[-2, -1]
X = [1, 1] and Y = [-2, -1] 		Sheet 4A
A2:B3
3. Let column-vector R = [R1, R2,...] be the absolute values of z1, z2, ... Compute R in the cells next to X and Y. For X = [x1, x2] and Y = [y1, y2],
R1 = (x12 + y12)1/2 and R2 = (x22 + y22)1/2 		Sheet 4A
C2:C3
4. Let column-vector q = [q1, q2,...] be the imaginary arguments of z1, z2, ... Compute q in the cells next to R. q1 = + Arccos (x1/R1) if y1 > 0,
and q1 = - Arccos (x1/R1) if y1 < 0.
q2 = + Arccos (x2/R2) if y2 > 0,
and q2 = - Arccos (x2/R2) if y2 < 0. 		Sheet 4A
D2:D3
5a. Let column-vector F = [F1(Z), F2(Z),...] be a set of functions based on the equations. F1(Z) = z13z25 - 6z1 z2 - 17
F2(Z) = 7z12z2 - 3z1 z23 + 5 		not entered
5b. Resolve F into real and imaginary parts, F = Fr + iFi. Compute Fr and Fi in the cells next to q. Elements of vector Fr:
Fr1 = R13R25Cos(3q1+5q2) - 6R1R2Cos(q1+q2) -17
Fr2 = 7R12R2Cos(2q1+q2) - 3R1R23Cos(q1+3q2) +5

Elements of vector Fi:
Fi1 = R13R25Sin(3q1+5q2) - 6R1R2Sin(q1+q2)
Fi2 = 7R12R2Sin(2q1+q2) - 3R1R23Sin(q1+3q2)

Sheet 4A
E2:E3

 

Sheet 4A
F2:F3

6a. Let J be the Jacobian matrix whose elements are the partial derivatives of vector F. dF1/dz1 = 3z12z25 - 6z2,   dF1/dz2 = 5z13z24 - 6z1
dF2/dz1 = 14z1 z2 - 3z23,   dF2/dz2 = 7z12 - 9z12z22 		not entered
6b. Resolve J into real and imaginary parts, J = Jr + iJi. Compute Jr and Ji in the cells next to Fi. Elements of matrix Jr:
(dF1/dz1)r = 3R12R25Cos(2q1 + 5q2) - 6R2Cos(q2)
(dF1/dz2)r = 5R13R24Cos(3q1+4q2) - 6R1Cos(q1)
(dF2/dz1)r = 14R1 R2Cos(q1+q2) - 3R23Cos(3q2)
(dF2/dz2)r = 7R12Cos(2q1) - 9R12 R22Cos(2q1+2q2)

Elements of matrix Ji:
(dF1/dz1)i = 3R12R25Sin(2q1+5q2) - 6R2Sin(q2)
(dF1/dz2)i = 5R13R24Sin(3q1+4q2) - 6R1Sin(q1)
(dF2/dz1)i = 14R1 R2Sin(q1+q2) - 3R23Sin(3q2)
(dF2/dz2)i = 7R12Sin(2q1) - 9R12 R22Sin(2q1+2q2)

Sheet 4A
G2:H3

 

 

Sheet 4A
I2:J3

7. Let matrices 1/Jr and 1/Ji be the inverses of Jr and Ji. Compute 1/Jr and 1/Ji in the cells next to Ji. In Excel, the element of 1/Jr in Row R, Column C is = INDEX (MINVERSE (Jr), R, C), where Jr is the cell reference of matrix Jr. Sheet 4A
K2:L3
M2:N4
8. Let matrix Marie be defined Marie = 1/Ji*Jr + 1/Jr*Ji. Compute Marie in the cells next to 1/Ji. In Excel, the element of Marie in Row R, Column C is = INDEX (MMULT (Matrix 1/Ji, Matrix Jr), R, C) + INDEX (MMULT (Matrix 1/Jr, Matrix Ji), R, C) Sheet 4A
O2:P3
9. Let matrix 1/Marie be the inverse of Marie. Compute 1/Marie in the cells next to Marie. In Excel, the element of 1/Marie in Row R, Column C is = INDEX (MINVERSE (Marie, R, C) Sheet 4A
Q2:R3
10. Let column-vector Vince be defined Vince = 1/Ji*Fr + 1/Jr*Fi. Compute Vince in the cells next to 1/Marie. In Excel, the element of Vince in Row R is
= INDEX(MMULT(Matrix 1/Ji, Vector Fr),R,1)
+ INDEX(MMULT(Matrix 1/Jr, Vector Fi),R,1) 		Sheet 4A
S2:S3
11. Let column-vector Vito be defined Vito = 1/Ji*Fi - 1/Jr*Fr. Compute Vito in the cells next to Vince. In Excel, the element of Vito in Row R is
= INDEX (MMULT (Matrix 1/Ji, Vector Fi), R, 1)
- INDEX (MMULT (Matrix 1/Jr, Vector Fr), R, 1) 		Sheet 4A
T2:T3
12. Let column-vector DX be defined
DX = 1/Marie * Vince. Compute DX in the cells next to Vito. 		In Excel, the element of DX in Row R is = INDEX (MMULT (1/Marie, Vince), R, 1) Sheet 4A
U2:U3
13. Let column-vector DY be defined DY = 1/Marie * Vito. Compute DY in the cells next to DX. In Excel, the element of DY in Row R is
= INDEX (MMULT (1/Marie, Vito), R, 1) 		Sheet 4A
V2:V3
14. Let W = U + iV. Compute the next approximation of W with U ~ X - DX and V ~ Y - DY Compute vectors U and V in the cells below X and Y. Sheet 4A
A5:A6
B5:B6
15. Repeat computations: Copy the rows of functions downward until X and Y converge, and Fr and Fi approach (0,0,...). U = [0.396336, 1.201338]
V = [-1.259594, -0.891433] W = [0.396336 - i1.259594, 1.201338 - i0.891433] 		Sheet 4B
A20:B21

 

Index

URL: http://members.aceweb.com/patrussell/approximations/procedures.htm
Unpublished Work. © Copyright 2001 Pat Russell. Updated April 17, 2009.