17.7 Implementing Cramer's Rule on a Spreadsheet

We can accomplish by following the plan here:

First we do some preparation. This is the slightly tedious part.

1. Enter your array of coefficients of your variables + the right hand side in some convenient place, starting, say in AA11 as upper left corner.

2. Produce the array A*ln(i+j) where i is the row number and j the column number and A is set to 10-8, with upper left corner in BA11 (This is to protect against dividing by 0)


Put =AZ10+1 in BA10 and copy to the right ten or so columns.
Put = AZ10+1 in AZ11 and copy down ten or so rows.
Put =10^-8 in BA9.
Now you have row and column labels and your small constant.
Then put in BA11:=$BA$9*ln(BA$10+$AZ11) and copy or fill this to the right and down ten or so rows and columns.
That does it.

3. Add your input data from step 1 and the array from step 2 together by entering into A11, the instruction =BA11+AA11.

4. Enter 1 in B2 and copy it into the rectangle with corners B2, B10 Z2 and Z10.

5. Put into the entry in row 11 and the column immediately after the column containing the right hand side of your equation, the entry =A11 and copy or fill this entry down to row 20 and far enough to the right to copy the entire array of coefficients.

You now should have your original data in the form (coefficients, right hand sides, coefficients), with small additions to protect against dividing by 0, and 1's above it all.

Now we are ready for the main step.

6. Enter into square A21 the instruction =(A11*B12-A12*B11)/B2, and copy or fill this across to Z11 and down to A91 and Z91.

This is all there is to it. Somewhere down below will be the determinant of your coefficients in column A, and, apart from sign, the numerators of Cramer's rule, in order starting in columns B on.

All that is left to do is to read off the solution to your equations, and check them.

OK, where are these solutions?

That depends on how big your array was. If you have k variables, this will occur in row 10k+1. Thus A(10k+1) will be the determinant of the left hand side, and the contents of B(10k+1) and on to the right will be, up to sign, the numerator determinants of Cramer's rule.

And what about the signs?

If k is even, all the signs will be wrong. If k is odd, the odd signs will be right and the even ones wrong.

Eh? How come?

The numerator in Cramer's Rule has the right hand side in the position of the column corresponding to the variable being solved for. The determinant that is obtained in the columns of row 10k+1 will have the right columns, but in the wrong order. To set them into the right order requires shifting the columns to the right order.Each switch reverses a sign.

Let us check with k = 3 and k = 4. If we call our variables v1, v2, ..., we can label the columns of our array as 1 2 3 rhs 1 2 3 in the k = 3 case, and 1 2 3 4 rhs 1 2 3 4, in the k = 4 case.

For k = 3 or 4 the content of A(10k+1) is the determinant of the columns (1 2 3) or (1 2 3 4). The content in the B column of that row is the determinant of (2 3 rhs), or (2 3 4 rhs), in the two cases. The content in the C column of that row will be that of (3 rhs 1) or (3 4 rhs 1). In the D column we have (rhs 1 2), or (4 rhs 1 2), and in the E column (1 2 3) and (rhs 1 2 3).

Verify from this that to rearrange these columns to the Cramer's rule forms, which are (rhs 2 3), (1 rhs 3), and (1 2 rhs) in the k = 3 case, and (rhs 2 3 4), (1 rhs 3 4 ), (1 2 rhs 4) and (1 2 3 rhs) takes an odd number of shifts in every k = 4 case and in the even k = 3 case.

So, what is the solution to the equations?

We copy the determinant of coefficients into A1 setting A1=A(10k+1) just to have it handy.

Then in the even k case we put the solution for v1 to vk in B1, C1, D1 by setting B1=-B(10k+1)/$A(10k+1) (you have to convert 10k+1 to an actual number here) and copying this to the next k - 1 columns to the right.

When k is odd you have to switch the signs of the v's with odd index.

And that is it.

How about checking?

We can do this by substituting the claimed solution back into the equations. This can be done by putting in say, box Z1 the entry
=$B$1*$AA11+$C$1*$AB11+ $D$1*$AC11+ ... until you get to the last variable vk.

If you copy this down column Z you should get the right hand side column for your equations. If you do, you have the right solution, when the content of A1 is not essentially 0.

What do you mean by essentially 0?

Computers, when calculating, do not have the ability to distinguish 0 from some very small number near it. Thus, you may get an entry that really should be 0 that is very close to 0 but not 0. You must learn to recognize that as 0. Here, because we add a small array to the original input we make it easy for the computer to be slightly off.

How can I tell if the answer is tiny or really 0 here?

One thing to try is to change the entry in BA9, which measures how much of a tiny array to add to the input data. If the changes your answer proportionally or more then the true answer is probably 0. Otherwise, you could try instead doubling the entries in your matrix. If that multiplies your determinant answer by 2k then the answer may be correct.

By the way, once you have set this up for a given k, you can change your data and immediately see the solution for the new data.

Of course you can save lots of space for small k by moving the main instruction up (and modifying it appropriately).

Exercise 17.12 Set this up for 6 by 6 arrays, and get it to work.