Vertex42 The Excel Nexus

In preceding sections, we used Euler's method and the Runge-Kutta method to solve the second-order differential equation y" - y = 0 by the shooting method. This differential equation can be solved readily by using the finite-difference method.

By comparison with equation 11-9, we see that a = -1, b = 0, c = 0. The elements of the coefficients matrix and the constants vector, calculated as before, are shown in Figure 11-13.

A |
B j C |
D |
E |
F |
G | ||

1 |
BVP by Finite-Difference Method | ||||||

2 |
Oenerai formula:y" + ay- bx + c | ||||||

3 |
y"-y=0 | ||||||

4 |
Exact y= slnhOO | ||||||

5 |
h= I 0.3 :j |
I | |||||

6 |
Matrix x y abc terms Constants | ||||||

7 |
0.1 0.1002 |
L | |||||

8 |
0 4 |
-1 |
0 |
0 : -2.09 -0.1002 | |||

9 |
0.7 |
-1 |
0 |
0 j -2.09 |
0.0000 | ||

10 |
1.0 |
-1 |
0 |
0 -2.09 |
0.0000 | ||

11 |
1.3 |
-1 |
0 |
0 -2.09 |
0 0000 | ||

12 |
1.6 |
-1 |
0 |
0 -2.09 |
0 0000 | ||

13 |
1.9 |
-1 |
0 |
0 -2.09 |
0.0000 | ||

14 |
2.2 |
-1 |
0 |
0 -2,09 0 -2.09 |
0.0000 | ||

15 |
2.5 |
-1 |
0 |
0.0000 | |||

16 |
2.3 |
-1 |
0 |
0 |
-2.09 |
-11.0765 | |

17 |
3.1 |
11.0765 |

Figure 11-13. Portion of the spreadsheet to solve the second-order differential equation y" -y = 0 by using the finite-difference method, (folder 'Chapter 11 Examples', workbook 'ODE-BVP', worksheet 'Finite Difference 2')

Figure 11-13. Portion of the spreadsheet to solve the second-order differential equation y" -y = 0 by using the finite-difference method, (folder 'Chapter 11 Examples', workbook 'ODE-BVP', worksheet 'Finite Difference 2')

• • 2 The errors in the finite-difference method are proportional to l/h , so decreasing the interval from h = 0.3 to h — 0.1 reduces the errors by approximately one order of magnitude.

In order to simplify the construction of the coefficients matrix, you can use the spreadsheet layout shown in Figure 11-14. The formula in cell 17, which has been assigned the name top, is

=IF(ROW()-ROW(top)=COLUMN()-COLUMN(top),INDIRECT("F"&ROW()), IF(ABS((ROW()-ROW(top))-(COLUMN()-COLUMN(top)))=1,1,0))

H |
1 |
J |
K |
L |
M |
N |
0 |
P |
0 | |

6 |
I | |||||||||

8 | ||||||||||

-2,09 |
1 |
0 |
0 |
0 |
0 |
0 |
0 |
0 | ||

9 |
1 -2.09 |
1 |
0 |
0 |
0 |
0 |
0 |
0 | ||

10 |
0 |
1 |
-2.03 |
1 |
0 |
0 |
0 |
0 |
0 | |

11 j |
0 |
0 |
1 |
-2.09 |
1 |
0 |
0 |
0 |
0 | |

12 |
0 |
0 |
0 |
1 |
-2.09 |
1 |
0 |
0 |
0 | |

13 |
0 |
0 |
0 |
0 |
1 |
-2.09 |
1 ! 0 |
0 | ||

14 |
0 |
0 |
0 |
0 |
0 |
1 |
-2.09 1 |
0 | ||

15 |
0 |
0 |
0 |
0 |
0 0 1 -2.0S |
1 | ||||

16 |
0 |
0 |
0 |
0 |
0 |
0 0 1 |
-2,09 | |||

17 |

Figure 11-14. Coefficients matrix to solve the second-order differential equation y -7 = o.

(folder 'Chapter 11 Examples', workbook 'ODE-BVP', worksheet 'Finite Difference 2')

Figure 11-14. Coefficients matrix to solve the second-order differential equation y -7 = o.

(folder 'Chapter 11 Examples', workbook 'ODE-BVP', worksheet 'Finite Difference 2')

To create the spreadsheet, do the following:

• Enter the desired range of x values in column A. This is best done by inserting rows within the range of x values, so as to preserve the formulas in the last row.

• Enter the boundary values of y in the first and last rows.

• Enter values or expressions for the coefficients a, b and c in cells C13, D13 and E13, and Fill Down.

• Select cell 17 and Fill Down, then Fill Right, to create the coefficients matrix.

Select the cell containing the formula for the results vector and Fill Down. Enter the formula by pressing CONTROL+SHIFT+ENTER.

The results vector is shown in Figure 11-15 and a plot of the results in Figure 11-16.

R |
S |
T I | |

5 |
Results y (exact) |
% error | |

01002 0,1002 | |||

0,4133 0.4108 |
0.6 | ||

9 |
07637 0.7586 I 0.7 | ||

10 |
1.1828 1,1752 |
0.6 | |

1:1 |
1.7083 1.6984 |
0.6 | |

12 |
2.3876 |
2,3756 |
0.5 |

13 |
3.2817 |
3.2682 |
0.4 |

14 |
4 4712 |
4.4571 |
0.3 |

15 |
6 0632 |
6.0502 |
0.2 |

16 |
8 2008 |
8,1919 |
0.1 |

17 |
11.0765 |
11,0765 |

Figure 11-15. Results vector from the solution of the differential equation y"-y = 0 by the finite-difference method, (folder 'Chapter 11 Examples', workbook 'ODE-BVP', worksheet 'Finite Difference 2')

Figure 11-15. Results vector from the solution of the differential equation y"-y = 0 by the finite-difference method, (folder 'Chapter 11 Examples', workbook 'ODE-BVP', worksheet 'Finite Difference 2')

Figure 11-16. Solution of the differential equation y" - y = 0 by the finite-difference method, (folder 'Chapter 11 Examples', workbook 'ODE-BVP', worksheet 'Finite Difference 2')

Was this article helpful?

## Post a comment