Index

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 2AA2: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 2AC2: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 2AE2: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 2AG2: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 2AA3:B3 7. Repeat computations: copy all functions down their columns until x and y converge. Solution: w = 1.00541938 + i1.16519460 Sheet 2BA9: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 3AA2: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 3AB2: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 3AC2: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 3AE2: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 3AG2:G3 7. Compute a new approximation using W ~ Z - DZ. Compute vector W in the cells below vector Z. Sheet 3AA5:A6 8. Repeat computations: Copy the functions downward until Z converges and F approaches (0,0,...). Solution: W = [1.110552, 1.845165] Sheet 3BA14: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 4AA2: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 4AC2: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 4AD2: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 4AE2:E3  Sheet 4AF2: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 4AG2:H3   Sheet 4AI2: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 4AK2:L3M2: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 4AO2: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 4AQ2: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 4AS2: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 4AT2: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 4AU2: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 4AV2: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 4AA5: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

URL: http://members.aceweb.com/patrussell/approximations/procedures.htm