Problem II Solved with Excel using the Iteration
The problem to be solved is
The finite difference formulation of this problem is
Rearrange the differential equation to the following form.
The iterative scheme can be represented as
where the superscript k represents the value of the quantity at the k-th iteration. Thus, one takes values at the k-th iteration and calculates the values with the superscript k+1. These values are then used in the equation again, and again. The process repeats until the values no longer change, to within a specified tolerance specified by you. (The iteration scheme presented is a Jacobi iteration scheme (link). When it is implemented in a spreadsheet it will be a Gauss-Seidel iteration (link), depending on the order of the calculations done by the spreadsheet.)
These equations are applied here using Microsoft Excel Spreadsheet, but the commands are generic ones that most spreadsheets allow. Consider the grid shown in the Figure.
The first row is reserved for the value of y at each grid point. The left-most cell is the value of y at the first point, and the right-most cell is the value of y at the last point. Since these values are known we simply insert the known values in the cells. For the points in between, we insert the equation given above.
It is convenient if we define some quantities, first, though, to simplify the algebra. Let the second row represent the value of x at the node i. The value of ∆x is placed in cell A4; first use 0.5. The first value of x, 0, is placed in the cell A2. Then the equation is written in cell B2 and copied to the right.
B2: = A2+$A$4, copied to the right
The $A$4 insures that when the cell formula is copied to the right, the next cell has B2+$A$4, then C2+$A$4; in other words, the A4 location is absolute and is not incremented during the copying. Once this is done, the second row should show the values 0, 0.5, 1.0.
Since the term involving x and ∆x is used constantly, its value is placed in the third row. The first column uses:
A3: = $A$4*$A$4*(1A2*A2)/2., copied to the right
Then the formulas for the cells involving yi are simply:
B1: = 0.5*(A1 + C1) + B3, copied to the right.
When you are placing these formulas in your spreadsheet, the program, will give you a warning since you are defining a circular reference, that is some cells are defined in terms of other cells,
which are in tern defined in terms of the the first cells. To begin the calculation in Microsoft Excel you have to choose Tools, then Preferences, then Calculation, and check the Iteration Box. That tells the computer that you will be performing iterations. (You may wish to prepare the spreadsheet with 'Iteration' turned off and turn it on when preparation is complete. This will prevent getting weird answers during the preparation when all the cells are not complete. This is especially true for non-linear problems.) There you can also set the maximum number of iterations to 500, and the convergence parameter to 10-6. After you choose Calculate Now the spreadsheet should look like this.
| 0 | 0.59375 | 1.000000 |
| 0 | 0.5 | 1 |
| 0.125 | 0.09375 | 0 |
| 0.5 |
How do we check our work? First, we make sure that the numbers we put in are correct. We check cell A4, which has the value of ∆x; 0.5 is correct. Then we check the second row, which shows the location of the grid points. They should be ∆x = 0, 0.5, 1.0, and these are correct. We then check the third row. Since we copied the formula, we only need to check one cell. For cell B3 the value should be
and this checks as well. (Note that a check of cell A3 would not suffice since x = 0 for column A and x, x2, or myriad other formulas give identical answers.) Lastly we check the cell B1. We can use the numbers in A1 and C1, since the cell formula does, too. We get
Since these calculations agree, we conclude we have set the spreadsheet up correctly. There is one final check that can be done. Notice in the formulas that if we set ∆x = 0, then the third row is all zero. This is equivalent to saying there is no heat generation. Under those circumstances the solution to the problem is a straight line. Set cell A4 to 0 and see. The second row is zero, too, but as long as the third row is zero, the second row is essentially not used. Since we get the straight line we have added confidence in our solution.
Next do the same thing with a ∆x = 0.25. The spreadsheet results are shown in the Table.
| 0 | 0.3242188 | 0.5898438 | 0.808594 | 1 |
| 0 | 0.25 | 0.5 | 0.75 | 1 |
| 0.03125 | 0.0292969 | 0.0234375 | 0.013672 | 0 |
| 0.25 |
Note that the value with ∆x = 0.25 is very close to the one derived using ∆x = 0.5. To be absolutely sure that the spreadsheet is prepared correctly, this spreadsheet is checked in the same way as for ∆x = 0.5. This is especially important since the check of cell B1 involved using A1,which was zero. Thus, while B1 was correct for ∆x = 0.5, in the case of ∆x = 0.25 we need to test cell C1, which uses cells B1 and D1, neither of which are zero. Finally, use ∆x = 0.125. The answers obtained for y(0.5) are shown below (using a tolerance of 10-8).
| ∆x | y(0.5) |
| 0.5 | 0.59375 |
| 0.25 | 0.5898438 |
| 0.125 | 0.5888671 |
The numbers are close to each other. Furthermore the increments between the answers are:
(0.5, 0.25): 0.59375 0.5898438 = 0.00380
(0.25, 0.125): 0.5898438 0.5888671 = 0.00098.
and follow the Richardson extraplolation. The error should go as ∆x2; with a ∆x half as big, the error should be one-fourth as big. If we are in the region (small enough ∆x) where this applies, the differences should decrease by a factor of 4 each time. In this case 0.00380 / 4 = 0.00095, which is close enough to say the convergence is followed.
Notice that this solution is also the same derived using MATLAB. That is as it should be, since we are solving the same equations. The only reason for a difference is that we might not have set the tolerance to a small enough value. In the case of MATLAB we used a method which solved a set of linear equations exactly in one step, to within the accuracy of the computer. In the case of Excel we used an iterative calculation, and we told it when to stop. Needless to say, the more accurate we want the solution, the smaller is the tolerance, and the more iterations we need.
Take Home Message: A spreadsheet program with an iteration feature provides a convenient way to solve the finite difference equations.