1

CHAPTER 1

Taylor Series, Truncation Error, and Significant Figures

ContentsIntroduction ..................................................................................................................................... 21.1 The Taylor Series and Truncation Error ............................................................................... 4 1.1.1 The Taylor Series ............................................................................................................. 4 1.1.2 The Mean Value Theorem of Differential Calculus ........................................................ 4 1.1.3 Taylor's Theorem ............................................................................................................. 6 1.1.4 Taylor Series Remainder Term and Truncation Error ..................................................... 6 1.1.5 Alternate Forms of the Taylor Series ............................................................................... 71.2 Convergence and Taylor Polynomials of Degree .................................................................. 81.3 Truncation Error and Significant Figures ............................................................................... 12 1.3.1 Defining the True Error in a Numerical Approximation ............................................... 12 1.3.2 Estimating Significant Figures from the Normalized True Error, .......................... 13 1.3.3. Defining the Computational Error in a Numerical Approximation .............................. 13 1.3.4 Estimating Significant Figures from Normalized Computational Error, ................ 14 1.3.5 Significant Figures and Taylor Polynomials .................................................................. 141.4 Exploring Numerical Convergence with Excel and VB: A Tutorial ...................................... 15 1.4.1 Security Settings and VB Macros .................................................................................. 15 1.4.2 Creating User Defined Functions for the Maclaurin Sine Series ................................... 16 1.4.3 Using Insert Function to Enter a User Defined Function .............................................. 20 1.4.4 Number Formats for Cells ............................................................................................. 21 1.4.5 Saving Your Workbook as a Marco-Enabled Workbook .............................................. 22 1.4.6 Significant Figures for Sine Series Taylor Polynomials ................................................ 22 1.4.7 Observations on the Convergence of the Maclaurin Sine Series ................................... 231.5 Automating Spreadsheet Calculations with VB ..................................................................... 24 1.5.1 Fixed Loops and the ForNext Statement ................................................................... 25 1.5.2 Conditional Loops and the WhileWend Statement .................................................... 27 1.5.3 "Frozen" Workbooks and Stopping Execution of Infinite Loops .................................. 28 1.5.4 Dimensioned Variables .................................................................................................. 29 Example 1.2 A VB Function to Automatically Converge the Maclaurin Sine Series ............ 29 Example 1.2.1 Setting up the Spreadsheet .............................................................................. 29

2

Example 1.2.2 Creating the User Defined Function ............................................................... 30 Example 1.2.3 Entering an Array Function in the Spreadsheet .............................................. 31 Example 1.2.4 Discussion of the Results ................................................................................ 33 Example 1.2.5 The #VALUE Error ........................................................................................ 341.6 Finding and Correcting a Mistake in VB Code ...................................................................... 35 1.6.1Using the VB Compiler Reset Button ............................................................................. 35 1.6.2 Using the MsgBox Command for Finding Errors in VB Code ..................................... 361.7 Option Explicit ........................................................................................................................ 371.8 Chapter Summary ................................................................................................................... 37Exercises ....................................................................................................................................... 38Programming Exercises ................................................................................................................ 40 Introduction A numerical method is a mathematical procedure that produces an approximate solution to an equation or a system of equations. Through most of undergraduate algebra, calculus, and differential equations, engineers and applied scientists focus on finding exact (analytical) solutions to equations. These solutions usually take the form of algebraic expressions including trigonometric and transcendental functions. A numerical method instead produces numbers. When a mathematical problem has a solution, but the analytical solution does not exist, cannot be found, or is too complex to be useful, then we use a numerical method to find the numbers that satisfy the equations. Many of the equations that describe the real world of science and engineering do not have analytical solutions, so numerical methods are required. Because a numerical method produces numbers rather than expressions, solving a problem with a numerical method means determining significant figures. Not knowing the significant figures in a numerical solution means not knowing the solution. The numbers in a numerical solution are seldom exact, but instead contain some amount of error. The error arises from two sources, truncation error and round-off error. In a Taylor series approximation, limiting the number of terms in the series causes the truncation error. Finite word length in computer memory causes the round-off error. We address the issue of truncation error in this chapter but delay a discussion of round-off error for Chapter 2. The approach we develop in this chapter allows us to assess the accuracy of our numerical approximations even when no exact solution exists for comparison. To illustrate all of these concepts, as well as to develop an important tool for deriving and analyzing many of the numerical methods introduced in the rest of the text, this chapter explores the Taylor series expansion. Taylor's theorem is the cornerstone of numerical methods. In this chapter we also use the Taylor series to explain the very important concepts of truncation error and significant figures.

3

Throughout the subsequent chapters of this book we make extensive use of the Taylor Series to derive many of the numerical methods we introduce and explore. We also use the Taylor Series to understand how a numerical method converges to an approximation of the solution with the desired number of significant figures. In the process we explore some important practical applications of the Taylor Series, such as its use in calculators and computer programs. You should not leave this chapter without understanding Taylor series, truncation error, convergence, and significant figures! Finally, we use Excel and Visual Basic (VB) in this chapter to approximate a function using its truncated Taylor series. This illustrates how an analysis of truncation error can be used to estimate significant figures, but it also serves as a tutorial for the reader who is unfamiliar with Excel or with principles of simple computer programming. During our discussion we introduce some basic tools for using Excel's VB editor to construct computer programs for use in Excel spreadsheets. The tutorials in Examples 1.1 and 1.2 show how to create an Excel spreadsheet, open the VB editor, write VB code to create one or more user defined functions, and then use the code in a spreadsheet. In the process we illustrate most of the basic Excel and VB programming tools required for the numerical methods in the rest of the book. If you spend the time to understand the programs in this chapter, you will have no difficulty with the programs in the rest of the book. Because we will be looking at mathematical expressions, Excel menu commands, Excel worksheet function, and VB procedures, the text in this book uses the following typographic conventions:

1. Mathematical expressions are typed in Cambria Math italics:

2. Excel and VB Editor menu commands appear in bold: "Click Insert on the Menu Bar, choose Object..."

3. Excel worksheet functions are in Arial font, all caps, with arguments italicized and with required arguments in bold (as per Microsoft's convention): FACT(number)

4. Excel spreadsheet formulas usually appear in Arial font on a separate line following a leading equal sign:

=1-LOG(ABS(F7)) 5. VB code is in Arial font. Complete VB procedures are displayed as figures:

Function Ti(i, x)Ti = (-1) ^ (i - 1) * x ^ (2 * i - 1) / WorksheetFunction.Fact(2 * i - 1)End Function

4

6. Keyboard sequences are indicated by capitalizing the key to be pressed and placing the name of the key in a box. For example, to indicate that the user is to press the F11 key while holding down the Alt key we write: Alt + F11

Now let us begin our study of numerical methods with a study of Taylor series expansions.

1.1 The Taylor Series and Truncation Error 1.1.1 The Taylor Series Taylors theorem, named for English mathematician Brook Taylor (1685 to 1731), is the most important theorem in numerical methods. Before we turn to a precise statement of the theorem and the remainder term associated with it, let us look at the basic concepts behind the Taylor series. A function's Taylor series expansion represents it as an infinite series in powers of x. Here, for example, are two equivalent ways of writing the Taylor series representation of the exponential function:

1 ! !

!

! , (1.1)

!

The basic concept of the Taylor series is that we can represent a function in the vicinity of a known value of the function by its value and the values of its derivatives at that same point. In equation (1.1) the exponential function is expanded about its known value and the known value of its derivatives at 0. This idea is an extension of the mean value theorem of differential calculus. 1.1.2 The Mean Value Theorem of Differential Calculus The mean value theorem states that for any smooth curve on a closed interval there exists at least one point on the interval where the derivative of the function exactly equals the average derivative over the interval. Consider the function, shown in the Figure 1.1, with a known value,, at . The mean value theorem tells us that there must be at least one point, , where and . Looking at the function in Figure 1.1, we can see that where is the slope of the chord intersecting at and and is the slope of the tangent line at . We can obtain an estimate for, which we call, using the slope of the tangent to at : where .

5

Will be a good estimate of? That depends on how much difference there is betweenand. There are two important observations we can obtain from Figure 1.1. Our first observation is that as and get closer, then m1, m2 and m3 will all approach the same value, and will approach the value of. For we observe that lim and . So using and to approximate will work better and better as the distance from to gets smaller and smaller.

Figure 1.1 Using The Mean Value Theorem to Estimate f(b) from f(a) and f '(a)

Our second observation is that the error in using the approximation comes from the fact that the slope of the tangent line keeps changing between and. We can see in Figure 1.1 that for the function represented in the figure the tangent line has an increasing slope from to. That means that for the function shown in Figure 1.1 the second derivative of is greater than zero at : 0. This observation suggests that we could improve our estimate of if we could learn how to incorporate the information about into our extrapolation. In fact, it looks as though, for the function in the figure, the rate at which increases between and could also be increasing, which would imply that 0. If we could incorporate the information from both and into our extrapolation, then our estimate of should improve still more. This is exactly what we do with the Taylor series expansion of a function about the point :

! !

!

!

(1.2)

( )f x

( )f b

( )f a

1( ) ( )f b f am

b a

3 '( )m f ca c b

2 '( )m f a

x

x a x c

* ( )f b

x b

h b a

6

The Taylor Series expansion of at the point allows us to incorporate our knowledge of,, etc., into our extrapolation from to . Using the function's Taylor series to estimate, we get a much better approximation than we would get from using only. Additionally, as we use more and more terms from the expansion, our approximation should, in general, continue to get better. Of course, unless we use all of the terms in the expansionhard to do when the expansion is an infinite seriesthen some error always remains. The difference between and that arises from the dropped terms in the Taylor series is called the truncation error. Convergence is the phenomenon that occurs where our approximation, , gets closer and closer to the true value as we make smaller and smaller and as our calculation includes more terms in the Taylor series. 1.1.3 Taylor's Theorem Taylor's theorem states that if is a continuous function with continuous derivatives of all orders, then the Taylor series expansion of about the point , is:

! !

!

!

(1.3) where is close to , i.e.| | where is a small number. If a function is equal to its Taylor series expansion at any point between and , then the function is said to be analytic in the closed interval , . This expansion of is unique for all , . Clearly all the derivatives in the expansion must be finite at for the expression to converge. Requiring 1 assures that as more terms are used in the series, then the more accurately the expansion approximates, becauselim 0 for | | 1. When the Taylor series is an expansion about 0 then it is called a Maclaurin series. This is a very important special case of the Taylor Series, which we will look at more closely later in the chapter. 1.1.4 Taylor Series Remainder Term and Truncation Error We now truncate the Taylor series after -terms and let the remaining terms in the series be represented by the remainder term, :

! !

! (1.4)

There are several expressions for the remainder term. The integral form, shown without derivation, is

7

!

(1.5) Applying the mean value theorem to Equation (1.5) we obtain the mean value or Lagrange form of the remainder term:

1!

(1.6) The remainder term is just the difference between the function and its Taylor series representation truncated after -terms:

!

!

! (1.7)

Thus we call it the truncation error. The Lagrange form of the remainder term tells us that the truncation error is proportional to , so we say that the truncation error is on the order of , or . 1.1.5 Alternate Forms of the Taylor Series We have already seen two equivalent forms of the Taylor series in Equations (1.2) and (1.3). Another useful form of the Taylor series we will encounter in later chapters represents as an expansion from ,to , . Equation (1.4) then takes the form

!

!

!

(1.8) If we are marching from point to point in -steps of equal length, then the step size, , is given by /. It is now convenient to write our Taylor series in terms of the step size:

! !

!

! (1.9)

All of these forms of the Taylor series are exactly equivalent, but it will be more convenient to use one form or another depending on the application.

8

1.2 Convergence and Taylor Polynomials of Degree A polynomial in is a function of consisting entirely of a finite sum of terms, each term being the product of a constant and a non-negative integer power of:

(1.10) The degree of the polynomial is the value of the largest exponent of found in its terms. The following three functions, , , and , are examples of a first degree polynomial, a second degree polynomial, and a fourth degree polynomial, respectively:

1 3 32 3 11

2 37

Not only are polynomials an important and powerful concept in mathematics, science, and engineering, but there exist some special relationships between polynomials and Taylor series. First, if a Taylor series is truncated after the nth-term in the series, and the remainder term is omitted, the result is a polynomial of degree 1. Second, the Taylor series expansion of any polynomial is the same polynomial: Each and every polynomial is its own Taylor series expansion. This proof is left as an exercise. If only the first term in the Taylor series is used, then is approximated by a zero degree polynomial, a constant. If the first and second terms are used, then is approximated by a first degree polynomial, a straight line. When the first three terms are used, then is approximated by a second degree polynomial, a quadratic equation. We could, of course, continue this exercise ad infinitum, since there are an infinite number of terms in the expansion of an analytic function. The first five Taylor polynomials obtained by truncating Equation (1.1), the Maclaurin series representation of the exponential function, and omitting the remainder term are:

1 1 x

1 x 12

1 x 12 16

1 x 12 16

124

Plots of the exponential function and these five Taylor polynomials are shown in Figure 1.2 over the closed interval 0 3. There is obviously a large error in using any of these approximations at 3; however, the approximations get better as more terms are retained

9

from the series. Note that is a constant or zeroth degree polynomial; a linear or 1st degree polynomial; quadratic or 2nd degree; cubic; and quartic. The higher degree polynomials represent the function more satisfactorily over the entire interval.

Figure 1.2 Comparing the Exponential Function with the First Five Polynomials from Its Maclaurin Series

What happens if we reduce the distance from the expansion point? The results for the exponential function are shown in Figure 1.3, where we plot the function and its associated Taylor polynomials over the interval0 1. On the scale of Figure 1.3, the 4th degree polynomial is indistinguishable from the exponential function while the cubic polynomial also has greatly reduced truncation error. In fact, the 4th degree polynomial is accurate to 3 significant figures at 1. At 0.1 even the first degree polynomial is accurate to 2 significant figures while the 3rd degree polynomial is accurate to 6 significant figures. Figures 1.2 and 1.3 illustrate two aspects of the phenomenon of convergence in numerical methods: First, the approximation of the function by its Taylor series expansion becomes better as we retain more terms in the approximation. Second, the approximation improves as we reduce the distance between the evaluation point and the expansion point. Another way to state this is that the numerical approximation converges toward the exact value of the function both as we reduce the step size and as we increase the order of the truncation error. If the remainder term of Equation 1.4 is omitted then we have the -degreeTaylor polynomial of the function. We noted in section 1.1.4 that the remainder term is the difference between the function and its truncated Taylor series. Since the truncated Taylor series corresponds to a Taylor polynomial, the remainder term is then also the difference between the function and the corresponding Taylor polynomial:

(1.11)

0

5

10

15

20

25

0 0.5 1 1.5 2 2.5 3 3.5

fx

x

ex P4(x)

P3(x)

P2(x)

P1(x)

P0(x)

10

Figure 1.3 Results of Reducing the Distance from the Expansion Point for the Accuracy of Taylor

Polynomials We have already noted that the remainder term is equal to the truncation error resulting from representing by a Taylor polynomial of degree. Our truncation error is then bounded by the maximum value of the remainder term on the interval, which in turn is determined by the maximum value of the derivative of order 1 over the interval or step:

|| ||

! (1.12) where the subscript denotes the maximum value of the derivative on the interval from to . Example 1.1 The Maclaurin Sine Series While Taylor series expansions of unknown functions will be very useful to us in much of the rest of this book, some forms of the Taylor Series have very important practical applications. Have you ever wondered how your calculator is able to return a value for the at any value of? When the authors were undergraduates there were two ways to find a value of the sine function: estimate it from your slide rule or look it up in a table. Your calculator certainly does not contain a tiny slide rule inside. But it also does not contain a table of values of the sine function. Instead, your calculator does the same thing that mathematicians once had to do to create tables of sine function values: Your calculator uses a Maclaurin series expansion of the sine function to calculate . The program we will write for this example is basically the same program run by your calculator and by Excel.

0

0.5

1

1.5

2

2.5

3

0 0.2 0.4 0.6 0.8 1 1.2

fx

x

ex

P4(x)

P3(x)

P2(x)

P1(x)

P0(x)

11

As mentioned earlier, when 0 in a Taylor series expansion, we call the expansion a Maclaurin series. Because we know from the definition of the sine and cosine functions that0 0 and0 1, the Maclaurin series for the sine function is very simple:

Table 1.1. Evaluation of Maclaurin Sine Series Terms Term Sine series term Value at 0

1 0 02 0 13 0 0 4 0 0 1 5 0 0 6 0 1

etc. etc. etc. Substituting these terms into Equation (1.3) we obtain the Maclaurin sine series:

0 11! 0 02! 0

13! 0

!

!

! (1.13)

Another way to write the sine series is as a summation of terms calculated from a formula:

1

! (1.14)

,

If we choose to truncate the Maclaurin series we can find polynomial representations of with a finite number of terms. For example the 5th degree Taylor polynomial we obtain from the truncated series is:

3! 5!

A logical question to ask at this point is how Excel or a calculator or any application can use an infinite series to estimate the value of a function at some particular value of. With an infinite number of terms to be evaluated, it is rather obvious that no matter how fast the chip, the calculation can never be finished. The solution to this dilemma is that you do not have to evaluate all of the terms in the series; you only have to evaluate enough terms to know that you have approximated the solution to accuracy sufficient for your application. Most calculators will carry between 9 and 16 significant figures. Excel calculates 16 figures and displays 15 significant.

12

The reason that we can get away with evaluating only a finite number of terms in the series is that, when the series converges, then each subsequent term in the series is smaller than the preceding term. Eventually, all the remaining terms in the series are too small to affect even the last significant figure you want to calculate, and we can stop. In order to get to the point where we have an easy-to-evaluate condition for stopping our calculations, lets take a closer look at the errors involved in approximating an elementary function using a truncated Taylor series, and see how those errors are related to the significant figures in our final result. 1.3 Truncation Error and Significant Figures 1.3.1 Defining the True Error in a Numerical Approximation A numerical method produces an approximation of the solution to a mathematical problem. We do not really know the solution until we know the number of significant figures in our approximation. In evaluating the accuracy of a numerical estimate, there are two sources of error, truncation error and round-off error. Truncation error arises from the terms not evaluated in the Taylor series and from the non-zero distance between the expansion point and the evaluation point. Round-off error is caused by the finite word length of the computer performing the calculations. We discuss round-off error in Chapter 2. Throughout the following discussion we will be concerned with two types of error. We call the first type true error (). We use the true error when we know the exact or true value of a mathematical function or equation. We call the second type of error computational error () and use it where the true value is not known or not used. We will define the true error in terms of the true value of the function and the computed estimate for the value of the function:

Hence, the true error is given by:

(1.15) Note that the true error contains both the truncation error and the round-off error in the computational estimate. Because the true value can be very large or very small, the true error must be normalized in order to be of use to us. The normalized true error () is defined to be:

(1.16)

13

1.3.2 Estimating Significant Figures from the Normalized True Error, In science and engineering we perform calculations using the number of figures we know to be exact, plus one additional figure carried to preserve the exact figures from round-off error. The figures we use in our calculations are called significant figures, so the significant figures in an estimate are the number of figures that are exact, plus one figure that is approximate. As the absolute value of the normalized true error gets smaller, we can use it to obtain a good approximation for the number of significant figures in a numerical estimate. Here we wish to define the term , which represents the number of significant figures in a numerical estimate obtained by comparing the computational estimate to the true value. Therefore is the largest non-negative integer for which

|| 10 where || means the absolute value of the normalized true error. Taking the base-10 logarithm of both sides of this equation we have:

|| 10 1 or

1 || (1.17) This equation works well when we know the true value, but there is a major problem when we do not: How can we use Equation (1.17) to determine when we have evaluated enough terms in an expansion if we do not know the true value of the function? We can get around this problem by assuming that our calculations are converging toward the true value, and then using our best estimate of the function to obtain an estimate of the true error, which we then use to get an estimate of the number of significant figures! Instead of the true error, we will use what we can call the computational error, which is the difference between the best estimate of the true value, and the next best estimate of the true value. This may sound like a circular definition, but it works because for a convergent Taylor series, each term in the expansion must have an absolute value smaller than that of the previous term. 1.3.3. Defining the Computational Error in a Numerical Approximation In real world problems of interest for using numerical methods, we do not know the exact, true, or analytic value of a function; thus, we introduce what we will call the computational error. It is defined as follows:

14

and applies when the calculations are converging toward the true value. Because the computational error can be very large or very small in absolute value, we introduce the normalized computational error () which is defined to be:

/ (1.18) 1.3.4 Estimating Significant Figures from Normalized Computational Error, The number of significant figures based on the normalized computational error is then

1 || (1.19) 1.3.5 Significant Figures and Taylor Polynomials Let us now examine the use of computational error to estimate significant figures using as an example the case of a Taylor polynomial. Let be an analytic function with continuous derivatives of all orders evaluated at. If we approximate the value of by its Taylor polynomial of degree, , then the corresponding true error is given by

The normalized true error is

/ and the number of significant figures in the estimate is given by

1 (1.20) Correspondingly, the number of significant figures for a Taylor polynomial, based on the computational error is

1 (1.21) Note that when the estimate is converged to the limit of the word length in the computer, the computational error will go to zero. The || is undefined if 0, but when we are calculating the this simply means that the number of significant figures in the computational estimate of is equal to the number of significant figures carried by the computer. In terms of Equation 1.21, this will occur when, within the significant figures available for calculations, .

15

1.4 Exploring Numerical Convergence with Excel and VB: A Tutorial In this section we examine the convergence of the Maclaurin sine series using Excel and user defined functions written in VB. The material is presented almost as an Excel tutorial, so the reader may want to study it with a computer available and with an open Excel workbook. As with many of the numerical methods covered in the text, the same task can be accomplished using only spreadsheet functions, or by combining a spreadsheet with one or more user defined functions written in VB code. This exercise will illustrate the advantages of automating a calculation with a user defined function. In the process we use most of the VB commands required to program the numerical methods covered in the rest of the book. 1.4.1 Security Settings and VB Macros The user defined functions we write to implement a numerical method are called "macros." Macros automate repetitive spreadsheet calculations. Because macros can be shared between Excel users and because some users write malicious macros that can damage another users data, Excel's default settings disable all macros. Before we can use the VB coded functions we will be writing, we have to change the security setting in Excel. If you wish to follow the next several sections using your computer, this is a good time to turn it on and open Excel. To use the VB code for the user defined functions in this book you will have to enable macros in your Excel security settings. To enable macros in Excel 2003, go to the Menu bar, click Tools, then Marcos and then Security. To run the user defined functions we will write in this course you must set your security to Medium or Low. Setting security to Low is the most convenient setting, but if you choose this level you must remember not to open Excel files from untrustworthy sources without first scanning them for malicious code. For Excel 2007 click the Office button at the upper left of the Excel window, the click Excel Options at the lower right of the menu that appears. On the Popular menu select Show the Developer tab in the Ribbon; on the Trust Center menu select Trust Center Settings at the lower right, and then Macro Settings, then click the button Enable all macros. This is the most convenient security setting for the exercises in this book, but the user must then remember to be careful about opening spreadsheets not obtained from a trusted source. The more cautious (or forgetful) user might settle for the inconvenience of choosing, instead, Disable all macros with notification; this will require you to enable macros every time before running the VB programs used in this book. IMPORTANT: When saving an Excel 2007 workbook containing a macro, you must choose to save the notebook as a macro-enable workbook with the .xlsm file extension. If you save the workbook as a simple Excel workbook with the .xlsx file extension, your VB code will not be saved and all of your work will be lost. When opening an existing 2007 workbook, if the workbook contains a VB macro, then it will have the .xlsm file extension, which prevents you from unintentionally opening a workbook containing a malicious macro. If, however, you open a

16

workbook saved in Excel 2003 format (file extension .xls), and it contains a macro, that macro will run automatically if you have selected Enable all macros under Macro Settings. 1.4.2 Creating User Defined Functions for the Maclaurin Sine Series The user defined functions we will write with the VB editor are accessed from the Excel spreadsheet the same way Excels built-in functions (such as the SIN(number) function or the EXP(number) function) are accessed. To create VB code for a user defined function you must open Excel's Visual Basic Editor. In Excel 2003 you can open the editor by choosing Macro from the Tools menu, then Visual Basic Editor from the submenu. In Excel 2007 choose the Developer tab on the Excel Ribbon, then select Visual Basic from the Code menu. The fastest way to open the VB editor, however, for both versions of Excel, is to use the keyboard shortcut Alt + F11. Let's now use Excels Visual Basic editor to create a user defined function for calculating the individual terms,, in the Maclaurin sine series, which we derived above (Equation 1.14). First we need to prepare the spreadsheet where we will implement the new function. All VB macros in Excel are accessed through a spreadsheet. In your workbook right click on the spreadsheet tab labeled Sheet1 (look to the lower left of your open spreadsheet) and rename the sheet Sine Series Terms. Next enter the column headings in cells A1, B1, and A4:D4 shown in Figure 1.4

Figure 1.4 Spreadsheet Column Headings for Maclaurin Sine Series Exercises

While we could easily enter the formula in Equation 1.14 into the spreadsheet cells under the Ti_Sin(x,i) column heading, we instead use the VB editor to create a user defined function, Ti_Sin(x,i). From the Excel window, open the VB editor by pressing the F11 key while holding down the Alt key. The window that opens should look similar to that in Figure 1.5. The VBProject window, on the left side of the screen, lists as Microsoft Excel Objects the names of all of the worksheets that are open in the Excel workbook. If you have any other Excel workbooks open you will also see a list of all the spreadsheets open in these workbooks, as well. If you are not an experienced user it is seldom a good idea to have more than one workbook open at a time while creating a macro, as you may inadvertently link the workbooks. So, if you do have any other workbooks open, you should probably close them immediately.

17

Figure 1.5 Open the VB Editor by Pressing Alt + F11

You now see a blank grey screen on the right as in Figure 1.5. Click Insert on the Menu Bar at the top of the VB editor and select Module. In the white frame that opens on the right hand side of the window type Function Ti_Sin(x,i) and press Enter. The VB editor automatically adds End Function. The variables inside the parenthesis are the function arguments. User defined function Ti_Sin(x,i) needs two input arguments, x and i, as the value of each term in the Maclaurin series is a function of both variables. The input arguments must be separated by commas. You can enter more than one user defined function in a single module; the VB editor places a heavy line after each END Function statement. You can create as many functions as you want in a single module. You can also insert as many modules as you wish into a VBAProject using the procedure above. This will often help organize a project. If you want to give helpful names to your modules, click View on the VB editor Menu Bar and select Properties Window. A new window will open in the lower left of you editor window, with a line listing the default name of the module you have inserted into the project window, cleverly named Module1. You can change the name of your module by typing the new name in the (Name) line of the Properties Window. The name you give the module must be different from that of any function name that is contained in any module in your project, or you will get an error when you try to run the macro from the spreadsheet. A valid name cannot contain spaces or punctuation, but it can contain the underline character, "_".The module name can be modified to be different from the VB user function name without error as follows: The user defined function Function1 can be in a module named Function1_ .

18

The name you give your functions and your modules also must be different from that of any existing VB function. You could not, for example, use SIN for a module name, since VB has a SIN function. For this tutorial let's name our new module MaclaurinSineSeries. Once a module has been created in the VB editor it can be saved into a module library folder. This is accomplished by right clicking on Module1 (or whatever you have named your module) and selecting Export from the menu that appears and then sending it to the module library folder. Module1 will be stored in the module library folder with the name Module1.bas, or in our case MaclaurinSinSeries.bas. The .bas file extension indicates the file contains VB code. Once a module exists in a module library folder, it can be imported from the library into any Excel VBAProject by right clicking on the Modules under the VBAProject heading, clicking on Import File and then clicking on the file you wish to import to your project. You will have to remember the name and location of the folder where the module is saved. The editor will not remember the location. Choose a convenient, easy to remember location for your modules folder; we will use it frequently. To continue, now, with the creation on our user defined function, in the space that the editor has left for you between Function Ti_Sin(x,i) and End Function type the formula for the terms in the Maclaurin sine series, Equation 1.14, using the correct VB operators and remembering that neither Excel nor VB allows for implicit operations:

Notice that the ^ symbol is the VB operator for an exponential, * indicates multiplication, and / indicates division. Both VB and Excel strictly follow the standard order of precedence for mathematical operators in evaluating a mathematical expression: The terms in parentheses are evaluated first, then the exponentials are evaluated, then the multiplications and divisions, and finally the remaining operations are executed in order from left to right. You should note that Excel (but not VB) has one notorious deviation from standard operator order: Excel gives a higher priority to negation than to exponentiation. In Excel the formula =-3^2 produces 9, not -9. In order to get -9 the formula you must enter in Excel is =-(3^2). While what you typed in the VB module window looks like a mathematical formula to you, it is actually an executable statement in VB called an assignment statement. The assignment statement tells the VB compiler that when the statement is executed the computer should take the values stored in the memory locations labeled i and x, perform the indicated operations in the indicated order, and then store the results in a memory location labeled Ti_Sin. You cannot use implicit multiplication in your assignment statements. If you type 2i VB will not understand that you mean 2*i. Also, you cannot type 2 x i as x is not an operator in VB.

Function Ti(i, x)Ti = (-1) ^ (i - 1) * x ^ (2 * i - 1) / WorksheetFunction.Fact(2 * i - 1)End Function

19

If you cannot see the entire formula you entered in the module window, click Tools on the Menu Bar, select Options, click the Editor Format tab, and choose a smaller font size for the editor. A 10-point font is usually small enough to let you see the entire formula. Notice that because Excels VB does not include a factorial function, we have to borrow the factorial function that is available in Excel as a worksheet function. The way we borrow an Excel function and use it in a VB executable statement is to type WorksheetFunction.function_name(argument list) in the statement, as we have done in our function Ti_sin(x,i). The name of Excels factorial function is FACT(n), and it takes only one argument, n, which can be any real number but which is truncated to a positive integer before evaluation. Your VB editor window should now look like the image in Figure 1.6

Figure 1.6 Creating a User Defined Function with the VB Editor

To use your custom function in Excel return to your Sine Series Terms spreadsheet by using Alt + F11 again or by clicking the Excel icon, , at the upper left of the VB editor window. For this exercise let's use the Maclaurin sine series to evaluate 1 using an increasing number of terms in the series and evaluating the significant figures after each new term. On your spreadsheet (see Figure 1.4) enter 1 in cell A2 and =sin(A2) in cell B2. In the cells below the column labeled i, starting with cell A5, enter the numbers 1, 2, 3, ..., 10. These are the numbers of the terms in the sine series that we will evaluate. We could use our new function in our spreadsheet just by typing

=Ti_sin($A$2,A5) in cell B5 and then copying that cell from B6 to B14. However, there are advantages to entering it by using the Insert Function dialogue bar. If we use Insert Function Excel will help us to remember the exact spelling of the name of the function and the number of arguments required.

20

1.4.3 Using Insert Function to Enter a User Defined Function You should see the Insert Function icon, , on the Formula Bar at the top of your spreadsheet next to the Formula Bar dialogue box (see Figure 1.7 where the Insert Function icon is selected). If you dont see it, then click View on the Menu Bar and select Formula Bar.

Figure 1.7 Using Insert Function to Enter a User Defined Function

Now click on the first cell under the column labeled Ti_Sin(x,i), which should be cell B5, then click the Insert Function icon. The Insert Function dialogue box should pop-up. You can also open the dialogue box by selecting Insert Function from the Formulas menu on the Menu Bar or by using the Shift + F3 keyboard sequence. On the select a category dialogue box, click the drop-down menu icon and select User Defined. You should see a list of all the user defined functions available to you on your copy of Excel. Scroll down until you find your function, Ti_Sin, and click on it. You should now see Ti_Sin(x,i) in bold just below the frame, followed by No help available. Click OK and you should see the Function Arguments window appear. We will learn how to add "help" to our functions in a later chapter. The cursor should be flashing in the data entry dialogue box labeled X. Just click on cell A2 on the worksheet, and you should see A2 appear in the data entry window. However, since we want this to be an absolute cell reference for this spreadsheet, press the f4 key after entering A2, and Excel should convert the relative cell reference A2 into the absolute cell reference $A$2. The dollar signs tell Excel not to increment the column and row references when you copy the formula to another cell. Now click on the data entry dialogue box next to the I. To enter the cell reference where you want Excel to look for the value to use for the variable I, just click on cell A5, though you can also just type A5. Since we always want to get the value of I from a cell in column A, press f4 repeatedly until there is a dollar sign before the A but not before the 5. Your spreadsheet should now look like the image in Figure 1.8.

21

Figure 1.8 Entering Cell References for Function Arguments

Click the OK button to save the formula to cell B5. Copy the formula in cell B5 to cells B6 through B14 by grabbing the black square in the lower right hand corner of B5 and dragging it down to cell B14. Cells B5 through B14 should now contain the first 10 terms in the Maclaurin sine series evaluated at the -value in cell A2. You have just calculated the first 10 terms in the Maclaurin sine series with a user defined function written in VB code. These are the values of the individual terms in Equations 1.13 and 1.14 for 1. The Maclaurin series approximation for the is the summation of the terms in the series. So, in cell C5 type =B5, but in cell C6 type =C5+B6 and press enter. Then copy the formula in cell C6 to cells C7 through C14. In our spreadsheet C5 now contains the value of the first term in the series. Cell C6 contains the sum of the first two terms in the series; C7 the sum of the first three terms in the series, etc. Each cell from C5 through C14 contains the Maclaurin series approximation for after the summation of the first-terms in the series, with varying from 1 to 10. 1.4.4 Number Formats for Cells Excel displays up to 15 significant digits when it does calculations, though the calculations actually carry 16 digits. Lets display all 15 digits in cell B2 and in cells C5 to C14. Select these cells, then right click on the selected area, and choose Format Cells. In the Format Cells pop-up window click the Number Tab, select Number, and set the Decimal places to 15, then click the OK Button. If you see ########## displayed in cells C5 to D24, just go to the top of the column and double click on the right borders for the B-column and the C-column. The column width should adjust to display all the places to the right of the decimal you chose from the Format Cells menu. Notice that for 1 radians, the Maclaurin series is identical to the value of given by Excel after the 9th term in the series. This is because Excel uses the Maclaurin sine series to evaluate the sine function and the 10th term in the series is < 10-15!

22

1.4.5 Saving Your Workbook as a Marco-Enabled Workbook IMPORTANT: This is a good time to save your Excel workbook and give it a name. If you are using Excel 2007 then you must save the workbook as an Excel Macro-Enabled Workbook or you will lose the VB code for the user defined function Ti_Sin(x,i) and have to re-enter the code, later. Click on the Office button in the upper left of your Excel window and choose Save As. From the options that appear choose Excel Macro-Enabled Workbook. A dialogue box will appear and you can choose the name and location to save your workbook. In this tutorial the workbook is named Maclaurin Sine Series. You may want to create a folder and subfolder for your workbooks, Numerical Methods and Chapter 1 Taylor Series, respectively. All Excel 2003 files are saved with the .xls file extension, regardless of whether or not the files contain macros. 1.4.6 Significant Figures for Sine Series Taylor Polynomials We are now at a good point to examine the behavior of the normalized true error and the normalized computational error () and their relationship to the significant figures in the Maclaurin series approximation of the sine function. We will first use our spreadsheet for the examination. This will help us prepare for writing a user defined function that will take the place of all the calculations performed in the entire spreadsheet! In cells D4 to I4 (Figure 1.9) type True Error, nte, sft, Computational Error, nce, and sfc, respectively. In cell D5 type the following formula to calculate the true error for the Maclaurin series approximation after -terms in the series:

=$B$2-C5 You can copy this formula to all of the cells from D6 to D14 just by double-clicking the small black square that appears in the lower right corner of the box after you press the Enter. The formula for the normalized true error () in cell E5 is

=D5/$B$2 which can then be copied down through cell E14. To find the estimated number of significant figures based on the normalized true error, enter the formula from Equation 1.17 into cell F5 and then copy it to cells F6 to F14:

=1-log10(abs(E5)) The computational error is calculated by comparing the two best estimates for the value of the function without reference to the true value of the function. This means that we cannot calculate a computational error until we have two estimates for the value of the sine function using the Maclaurin series. Starting, therefore, in cell G6, enter in cells G6, H6, to I6 the following formulas, respectively:

23

=C6-C5 =G6/C6

=1-log10(abs(H6)) Copy these formulas down to the ends of the respective columns. Your spreadsheet should now look like Figure 1.9. If the formatting of the values displayed in any of the cells in your spreadsheet looks different than what you see in Figure 1.9, you can change it, if you want to, by selecting the cells with the different format, right-clicking in the area of the selected cells, choosing Format Cells, then clicking the Number tab, and choosing the formatting you prefer.

Figure 1.9 Spreadsheet for Evaluating the Sine Function from the Maclaurin Sine Series with Estimates of

Significant Figures If your spreadsheet is not giving you the same values as those shown in Figure 1.9 above, you can compare the formulas in your cells to the formulas shown in Figure 1.10 by holding down the Ctrl key and then pressing the ~ key. The ~ key is usually on the upper left of your keyboard, just below the Esc key. 1.4.7 Observations on the Convergence of the Maclaurin Sine Series Lets make a couple of observations about the result of the calculations in the spreadsheet shown in Figure 1.9. First, both the true error and the computational error go to zero as the number of terms in the Maclaurin series increases. The true error goes to zero in fewer terms because the Maclaurin series is converging to the correct value of, and the true error uses that true value as a reference, whereas the computational error can only use the previous best estimate, which is always further from the true value than the best estimate. Consequently, the estimate of the number of significant figures in the truncated Maclaurin series is always too low when it is based on the computational error. Nevertheless, a conservative estimate of the significant figures is better than no estimate at all, which will be the case when we are solving problems where we do not know the true value and so cannot use it as a reference.

24

Figure 1.10 Cell Formulas Used in Spreadsheet Shown in Figure 1.9 as Revealed by the Ctrl + ~ Key

Sequence Next, notice that our algorithm for estimating the number of significant figures blows up when the error goes to zero. This is because the logarithm of zero is undefined. When we try to take the logarithm of zero Excel puts #NUM! in the cell where the operation was attempted, to tell us that one of the functions we used (LOG10(x) in this case) was undefined for the value of the argument. We could avoid seeing this undefined number error if we used a conditional statement in the cells to set the significant figures to 15 when the error becomes zero, but we will delay such refinements until later chapters. Finally, notice that we have used two Excel functions in the formulas in the cells, LOG10(x) and ABS(x). The function LOG10(x) returns the base-10 logarithm of the argument. In Excel the function LOG(number,base) returns the base-10 logarithm when base is set to 10, and also returns the base-10 logarithm of when base, which is not a required argument, is not specified. In Excel the natural logarithm is returned by the function LN(x). To minimize the confusion, we have chosen in this book just to use the LOG10(x) function in Excel for base-10 logarithms, LN(x) for natural logarithms, and to use the worksheet logarithm functions when in VB. Just to maximize the confusion, VB has a Log(x) function, which returns only the natural logarithm, but does not have a function that returns the base-10 logarithm; hence, we choose just to use the worksheet log-function from Excel when needed. The function ABS(x) returns the absolute value of . To better understand how VB executes Abs(x) and Log(x) and to see another way to find the base-10 logarithm of in VB, go to the VB editor enter abs(x) or log(x) in the help box at the far right of the menu bar. 1.5 Automating Spreadsheet Calculations with VB The study of the convergence of the Maclaurin sine series in the spreadsheet shown in Figure 1.9 is both very compact and very easy to examine. You probably noted, however, in preparing it, how many times the same calculation was performed. If we had wanted to study a value of

25

where dozens or even hundreds of terms were required for convergence, the spreadsheet would become quite inconvenient to examine, as well as tedious to prepare. VB and most other programming languages (such as FORTRAN) contain loop structures that are ideal for repetitive calculations such as those in our sine series spreadsheet. It is also possible to calculate multiple variables in the function macro and pass all of the values back to the spreadsheet using dimensioned variables. These two tools, loops and dimensioned or array variables, will enable us to replace entire spreadsheets with a single VB function macro. 1.5.1 Fixed Loops and the ForNext Statement In Example 1.1 we used VB to create a user defined function, Ti_Sin(x,i), that is very simple. Now, however we will write a user defined function that will both calculate the terms in the series and also add them up, just displaying the results.

Figure 1.11 User Defined Function to Sum First -Terms in the Sine Series

Return to your version of the spreadsheet in Figure 1.9. Note that in the spreadsheet we have used cells B5 to B14 to display the individual terms in the series and used cells C5 to C14 to display the running sum of the terms in the series. VB has a statement, the ForNext statement, that will allow us easily to sum the terms of the series as we calculate them. In the VB editor window of your project, enter the code from Figure 1.11 into the same module you used for Ti_Sin (x, i). After you enter this code your VB editor window should look like Figure 1.12. Note that our new function uses our first function, Ti_Sin(x,i). One custom function can call other custom function. This is a powerful tool for organizing a VB program.

Figure 1.12 Creating a Second User Defined Function in the Maclaurin Sine Series Module

Function Sum_Tn_Sin(x, n)' This function computes the sum of the first 'n' terms' of the Maclaurin Sine Series

Sum = 0For i = 1 To n

Sum = Sum + Ti_Sin(x, i)Next i

Sum_Tn_Sin = SumEnd Function

26

The function Sum_Tn_Sin(x,n) calculates and sums the first -terms in the Maclaurin series. Most of the work is done in the ForNext loop. The ForNext loop starts with i = 1 and then increments i in steps of 1 until it reaches i = n, executing the statements between the For and the Next at each step. The VB syntax of the For...Next loop is shown in Figure 1.13:

Figure 1.13 VB Syntax of For...Next Statement

Note that in the code for Sum_Tn_Sin(x,n) we have not indicated the Step increment shown in Figure 1.13. When the increment is not specified in the statement, it defaults to a value of 1. There will be times when we will want the increment to be something other than 1. We have also added two comment statements to the function, to help us remember what it does. In VB, when a line of code starts with an apostrophe (or single quote), that line is not executed but is simply displayed. Comment statements are also very helpful when a string of code is going to be used by someone who did not write it. To learn more about comment lines enter writing visual basic statements in the help box on the right of the VB editor menu bar. Lets now apply the function Sum_Tn_Sin(x,n) and compare to the results shown in the spreadsheet in Figure 1.9. Return to your spreadsheet named Sine Series Terms and enter the following column headings in cells B16 to E16, respectively (see Figure 1.14): n, Sum_Tn_Sin(x,n), nce, sfc. In cells B17 to B25 enter the following values for n, the number of terms in the Taylor polynomial: 2, 3, 4, 5, 6, etc. Now use Insert Function to enter your new user defined function, Sum_Tn_Sin(x,n) in cell C18, entering $A$2 as the location for the x-argument and B18 for the n-argument, then copy the formula in cell C18 to cells C19 through C25. The results of the formulas should be the same as those you got in cells C2 through C10. In cell D19 enter the formula for the normalized computational error from Equation (1.18), which in our Excel spreadsheet will look like this: =(C19-C18)/C19. The formula for the significant figures based on the computational error is entered in cell E19: =1-LOG10(ABS(D19)) and then copied to the rest of the cells in the column. If youve done everything correctly your spreadsheet should now look like the one in Figure 1.14.

For Counter = Start To End Step Incrementstatements

Next Counter

27

Figure 1.14 Comparing Results for User Defined Function Sum_Tn_Sin to the Spreadsheet Calculations

If the numbers in your spreadsheet are correct, but do not have the same format as those in Figure 1.14, remember that you can change the way the results of a formula are displayed by selecting the cells you want to reformate, right-clicking on the highlighted cells, selecting Format Cells, then clicking the Number tab and selecting your format. If your numbers are not the same as in Figure 1.14, you can compare the formulas you entered to those in Figure 1.15.

Figure 1.15 Formulas for Spreadsheet in Figure 1.14

1.5.2 Conditional Loops and the WhileWend Statement In section 1.5.1 we created a user defined function that automatically evaluated a specified number of terms in the Maclaurin sine series. What we really want, however, is a function that will evaluate only the number of terms required to converge the series to a chosen number of significant figures, without our having to know beforehand how many terms that will be. The

28

function we will write next, user defined function TSE_Sin_sf(x,sf), will replace almost the entire spreadsheet shown in Figure 1.14. It will use the Maclaurin sine series to calculate the value of to a specified number of significant figures, using only the number of terms in the expansion required to achieve the desired accuracy, and then display in the spreadsheet both the value of series and the number of terms. In order to write a function that can accomplish all of these things, we need to introduce a new type of loop structure, the conditional loop, and a new type of variable, the dimensioned variable. Let's look first at the structure of the conditional loop. The type of conditional loop that we will use is the While...Wend loop. The syntax of the loop is given in Figure 1.16.

Figure 1.16 the While...Wend Loop Structure

The statements within the loop can be any executable VB statements. Once program execution reaches the While...Wend loop, execution of the statements in the loop will begin if the LogicalExpression is true. Execution then begins starting from the first statement after the While and proceeding to the last statement before the Wend. After execution reaches the Wend the LogicalExpression is evaluated again. If it is still true, then the execution of the statements in the loop resumes, returning back to the first statement after the While. This process continues as long as the LogicalExpression remains true. Typical LogicalExpressions might be i < 20 or Abs(nce) < 10^(1-sf). The While...Wend loop is called a conditional loop because it executes the statements contained in the loop only on the condition that the LogicalExpression is true. 1.5.3 "Frozen" Workbooks and Stopping Execution of Infinite Loops Conditional loops can be dangerous in the sense that a programming mistake or a numerical error might turn them into infinite loops. In this case, once execution of the statements in the loop begins, it will not stop without outside intervention of the computer user. When this happens your Excel workbook will appear to "freeze" or become unresponsive. It is frozen only in the sense that it is executing the statements in the conditional loop over and over again, just as it was instructed to do. If you do not intervene to stop it, it will never stop. If your workbook freezes because of a VB macro that is executing an infinite loop, there are four ways of getting out of the loop. The first is to press the Esc key. This key is pressed in Excel to stop the execution of a macro; however, it may not work when the macro is stuck in the execution of an infinite loop. The next best way to get out of a frozen workbook is to use the Windows Task Manager. The Task Manager is accessed by pressing Ctrl + Alt + Del simultaneously, then selecting the Task Manager. Locate the frozen workbook under the list of running programs, and end execution. If this works you will lose the contents of the workbook. If the Task Manager is unable to end execution of the macro, or if Windows freezes, too, you can try turning off your computer. This also may not work, and again, if it does, any work that you have done since the last automatic save of your workbook will be lost. The last resort is to hold

While LogicalExpressionstatements

Wend

29

down the power button on the computer. This will force a shut down or a hot reboot. In either case, when Windows returns, all of the programs that were executing will be closed, and you can start again with a new workbook. Obviously it is much better to avoid getting into an infinite loop than to try to stop the execution of one. The While...Wend loop provides a very convenient way to do this. If a counter is placed as one of the statements in the loop, and this counter is incremented every time the loop executes, then one of the logical conditions that can be placed after the While statement can be that the counter is less than some maximum value. This will assure that the loop stops executing in a finite number of times. An example of this follows in the VB code for the next user defined function. 1.5.4 Dimensioned Variables Let's look next at dimensioned variables. In programming languages a variable is just a name used to refer to a memory location where values or references are stored. The results of calculations are stored in memory locations referred to in the program by their variable names. The most straightforward way of passing multiple values from a function macro back to the spreadsheet is to assemble all of the variables to be passed into an array variable. An array variable is just a variable that can be used with subscripts, such as answers(1), answers(2), etc. In order to use an array, however, the variable must be declared to be an array variable. In VB this is done using the DIM statement. The DIM statement must appear in the program before any executable statements such as assignment statements. In order to set up an array named answers that contains 10 elements, we would use the statement

This statement would allow us to assign different values to the variables answers(1), answers(2), etc., and then pass all 10 values back to the spreadsheet as an array, answers. We will need to use an array variable for our next user defined function. Later in the book we will want to use multidimensional arrays, with 2 subscripts for each element. Example 1.2 A VB Function to Automatically Converge the Maclaurin Sine Series We are now ready to write a VB function that will automatically converge the Maclaurin sine series to a specified number of significant figures, and return both the converged value of the series and the number of terms required for convergence. Example 1.2.1 Setting up the Spreadsheet Return to the workbook you created for Example 1.1, shown in Figures 1.9 and 1.14, above. Go to Sheet2 in your workbook, and if you have not already given it a useful name, rename it TSE_Sin_sf. Set up the spreadsheet to look like the one depicted in Figure 1.17. The formula in cell B3 is =sin(A3).

Dim answers(1 To 10)

30

Figure 1.17 Spreadsheet for User Defined Function TSE_Sin_sf(x,sf)

Example 1.2.2 Creating the User Defined Function We want our new function to return two values to the spreadsheet, the value of the Maclaurin series converged to the specified number of significant figures and the number of terms in the series required to reach that level of convergence. In order to return more than one value from the VB program to the spreadsheet, we have to use a dimensioned variable. We can call it anything we want, so lets call it ans for answers. Open the VB editor, go to the module in your VBProject and type Function TSE_Sin_sf(x,i) in the module window directly under the last End Function statement. Then enter the rest of the statements shown in Figure 1.18. It is not necessary to enter the comment statements for the function to work correctly. Note that the first line of the function, after the first comment block, is Dim ans(1 to 2). This is called a declaration statement and it tells the VB compiler to create two variable locations, one labeled ans(1) and the other labeled ans(2). The program also needs a memory location in which to store the sum of the terms in the series after they are calculated; this is the variable named Sum. We use an assignment statement to set it equal to the first term in the series before using it to accumulate the sum of the terms in the Taylor series inside the While...Wend loop. We also have to initialize the variable where we are going to keep a count of the number of terms evaluated, i, and the variable where we will store the calculated value of the normalized computational error, nce. If we do not initialize nce, it will automatically be set to zero by the VB compiler, and the While...Wend loop will not execute. The initialization of program variables occurs in the sequence of statements

By setting the 10 we assure that the While...Wend loop executes even if the user sets sf = 0, since our convergence criterion from Equation 1.19 can be arranged to yield

|| 10

i = 1Sum = Ti_Sin(x, i)nce = 10

31

Figure 1.18 VB Code for a User Defined Function to Automatically Converge the Sine Series

After the initialization portion of our program executes, we are ready to start calculating the sequence of terms in the Maclaurin series, summing them up, and checking for the number of significant figures in the summation based on the normalized computational error. We want to keep doing this as long as the number of significant figures is less than the specified number of significant figures, but to prevent getting stuck in an infinite loop we also count the number of terms that have been calculated, and then end the loop if we have not reached convergence after 20 terms. We do this by setting the logical test that must be true for the While...Wend loop to repeat. The test that must be true before the statements in the loop are executed again is

You could read the While...Wend loop as follows: As long as it is true that the absolute value of the variable nce is greater than 10 and it is also true that the number of terms already calculated is less than 20, then execute the statements between the While and the Wend. This is why we initially set nce to 10, so that the test is true when we first reach the loop. Now return to the VB editor and enter the rest of the code in Figure 1.18 into the module in your VBAProject. Example 1.2.3 Entering an Array Function in the Spreadsheet Our new function, TSE_sin_sf, is called an array function, meaning that it is able to return more than one variable value to the spreadsheet. However, just because the return variable, ans(1 to 2), is an array variable does not mean that all the elements of the array will be displayed

Function TSE_sin_sf(x, sf)' This function computes the sum of the Maclaurin sine series at x' converged to a desired (input) number of significant figures, sf' The values returned to the spreadsheet are ans(1) = sum, the value' of the series at x and ans(2) = n, the number of terms evaluated' in the While...Wend loop to achieve the sf-significant figures

Dim ans(1 To 2)' Dimension statement to create output vector, ans(1 to 2)

i = 1Sum = Ti_Sin(x, i)

' Initializes the counter for the number of terms calculated' in the While..Wend loop and sets Sum = the first term in the series

nce = 10While (Abs(nce)) > 10 ^ (1 - sf) And i < 20

i = i + 1new_Ti = Ti_Sin(x, i)Sum = Sum + new_Tince = new_Ti / Sum

Wendn = i

' The following statements return the output variables Sum and nans(1) = Sumans(2) = nTSE_sin_sf = ans

End Function

(Abs(nce)) > 10 ^ (1 - sf) And i < 20

32

automatically on the spreadsheet. To be able to have the entire array displayed in the spreadsheet, we have to enter the function into the cells where we want the array displayed, and we have to enter it in a special way. When the array is a single dimensional vector, like our ans(1 to 2) variable in TSE_Sin_sf(x,sf), all of the selected cells must be in the same row of the spreadsheet, as Excel interprets a one dimensional array as a row vector. The array can be shown as a column vector if we use the worksheet function TRANSPOSE(array). On the spreadsheet shown in Figure 1.17, select cells D3 and E3, then click the Insert Function icon on the Formula Bar. Select User Defined Functions, and then scroll down until you see TSE_Sin_sf; select TSE_Sin_sf and then click OK.

Figure 1.19 Using Insert Function to Enter an Array Function into Multiple Cells. Note that cells D3 and E3 are both selected when the function is entered. You must press Ctrl + Shift + Enter simultaneously to display multiple elements of the results of an array function. Clicking the OK button will display only the first element in the solution array.

IMPORTANT: At this point you need to be careful and go slowly. To display multiple elements of the solution array of an array function, the function must be entered using a special key sequence! In the Function Arguments window, enter $A$3 in the window for X, and C3 in the window for Sf. DO NOT click the OK button. Once your Excel window looks like Figure 1.19, instead of clicking the OK button, enter the Ctrl + Shift + Enter key sequence. The best way to do this is to hold down the Ctrl and Shift keys and then press Enter. This sequence tells Excel you want to enter the function as an array function.

33

Figure 1.20 Automatic Convergence of the Maclaurin Sine Series using a VB User Defined Function

After entering the function in cells D3 and E3, you can copy it down through the rest of the column. With cells D3:E3 selected, grab the solid black box at the lower right hand corner of the selected cells, and drag it down to cells D17:E17 to complete the spreadsheet. If you enter the key sequence correctly, your spreadsheet should now look like the image in Figure 1.20. Example 1.2.4 Discussion of the Results Function TSE_sin_sf gives us a powerful tool for examining the convergence behavior of the Maclaurin sine series as a function of both the distance of x from the expansion point and the number of significant figures we require for convergence. On the next empty spreadsheet in your workbook create the spreadsheet shown in Figure 1.21. The formulas in cell B3, B4, and C2:D2 are, respectively

=PI()/16 =$B$2+B2 {=TSE_sin_sf(B2,$A$2)}

The brackets on the formula {TSE_sin_sf(B2,$A$2)} tell us that it was entered as an array function using the Ctrl + Shift + Enter key sequence. The values of in the -column vary from /16 to 2 in increments of /16. Excel makes it easy to enter the value of correct to 16-digits (though only 15 are displayed) by using the Pi() function; in Excel Pi() is a function that takes no arguments, so it is necessary to use the parentheses, but not to put anything in them! The easiest way to create the plot in Figure 1.21is to use the Insert menu. Just select the cells in the columns labeled x, Sin(x) and n, including the column headings, then go to the Menu Bar and click on Insert, then Scatter Plot, and then Scatter with Smooth Lines. In order to create a secondary axis for the plot of the , the number of terms required to converge the series to the specified number of significant figures, right click on the line for , select Format Data Series, Series Options, and check Secondary Axis. In order to add axis labels and format the legend, click anywhere on the plot area then click on the Chart Tools tab that appears above the Menu Bar, select Layout, and then edit any of the available chart options. To add the line labels

34

and arrows, select Insert on the Menu Bar and then choose Smart Shapes or Text Box to find those options.

Figure 1.21 Exploring Convergence of the Maclaurin Sine Series using Function TSE_sin_sf(x,n)

The general shape of the plot for is as we would expect: As the distance from the expansion point of 0 increases, the number of terms required to reach the designated number of significant figures increases. It is a worthwhile exercise to examine how varies over the interval 0 2 when the number of significant figures is increased. But there are obviously anomalies at and 2. The problem at these points is our convergence criterion and its use of the normalized computational error to calculate significant figures. Since the series converges toward 0 at these values of, and we have the sum of the series terms in the denominator, our formula is magnifying the round-off error in the calculations by dividing the difference between the terms by a small number. The solution to this problem is to use the computational error instead of the normalized computation error to estimate the significant figures as the value toward which the series is converging becomes much less than 1. The student has the opportunity to explore this issue further in Exercise 5. 1.2.5 The #VALUE Error If we use our new function at 0 it returns the #VALUE! error message, as shown in Figure 1.22. The #VALUE! error occurs when an Excel function is expecting a number but instead receives text. What has happened in Figure 1.22 is that the Maclaurin series is expanded about x = 0, and every term in the sine series is exactly equal to zero when x = 0. As a result, the sum of the terms is always zero, and when we calculate the normalized computation error in our user defined function by dividing by sum we are dividing by zero. This causes VB to generate an error message indicating that we tried to divide by zero. When Excel receives the error message from our user designed function instead of a number, Excel displays the #VALUE! error. We

35

could easily fix this by inserting a conditional statement (see Exercise 5) in our user defined program that would set the answer equal to zero and stop executing the program. However, we will not worry about this detail in this chapter, as our goal at this point is not to perfect our user defined function, but to learn how an iterative calculation reaches convergence, and how to evaluate such a function using Excel.

Figure 22 The #VALUE! Error Generated by Excel. Indicates that the calculations in the function macro

have generated an error message.

Figure 1.23 Spelling Error in Function Name Produces a Compiler Error

1.6 Finding and Correcting a Mistake in VB Code 1.6.1Using the VB Compiler Reset Button Windows does not run the code for your user defined functions directly. It must first be translated into a computer language called executable code. This is done by a compiler. The compiler has to identify all of the functions used in the code, and all of variables, among many other tasks. If you make a typing mistake and spell a function or dimensioned variable name incorrectly, you will get a compiler error when you try to use your new user designed function, as shown in Figure 1.21, where the name of user defined function Ti_Sin(x) has been misspelled

36

as Ti_Sine(x). If there is an error in your code, such as an unrecognizable command or unknown function name, the compiler will select the line of code at which the error was encountered, highlight the name of the function in which the error was found, and freeze the compilation of the code until the error is corrected, as illustrated in Figures 1.22 and 1.23. In order to unfreeze the editor after correcting the error, you have to reset the editor by clicking the small square button you should see just below the R in Run on the tool bar, as shown in Figure 1.23, or select Reset from the Run menu.

Figure 1.22 VB Editor Frozen After a Compile Error Caused by Misspelling a Function Name. Unfreeze the

editor by clicking the square button under the R in Run. 1.6.2 Using the MsgBox Command for Finding Errors in VB Code If there are no syntax errors, no unknown functions, and no undeclared arrays in your VB code, the compiler will translate it into executable code even if it contains logic errors. Sometimes it can be very frustrating to try to find the error in the VB code for a macro. The MsgBox command can be employed to help locate where in a program an error occurs and what is the nature of the error. When a macro is run from the spreadsheet, the value of any variable in a VB function may be displayed on the computer screen at any point in the execution of the program by inserting a MsgBox statement into the code at the point where you would like to examine the values of the variables during execution of the code. For example in the VB function in Example 1.2, we have VB code for the function TSE_Sin_sf(x, n). If we wanted to examine the values of the variables i and Sum each time through the While...Wend loop we would type the following statement inside the loop after the variables are update during execution of the code:

MsgBox ("for i = " & i & " Sum = " & Sum)

37

MsgBox can be an effective tool when debugging VB code to check the value of a variable to be sure it is correct in the code. You can "turn off" a MsgBox command by using the single quote symbol to convert the command line into a comment statement then turn it back on by removing the quote. To read more about the MsgBox function enter MsgBox in the VB help box on the VB Menu Bar.

1.7 Option Explicit One of the most helpful things you can do to find errors in your VB programs is to start each module with the Option Explicit statement. When your module starts with this statement, then your program will not successfully compile until every variable has been declared with a DIM statement. While this might at first seem like an unnecessary chore, it is actually a shortcut for debugging a program. If you have misspelled any of your variable names anywhere in the program, then your program will not compile and the VB compiler will highlight the misspelled name. This can save you hours of frustration. In most of the programs we show in the text we will not include either the Option Explicit statement or a complete list of declared variables. This is simply for the sake of succinctness and simplicity, and to eliminate clutter for the reader. When any of the programs in the book are used, we recommend that the module begin with the Option Explicit statement and that the user declare all variables using the DIM statement. Beyond helping with debugging your program, the actual machine language version of your program created by the compiler will be more compact and more efficient if you declare all of your variables.

1.8 Chapter Summary Chapter 1 defines a numerical method as a mathematical procedure that produces an approximate solution to an equation or system of equations. From there we proceed to illustrate the process of approximation by examining Taylor series approximations of functions. We start with reviews of Taylor's theorem and the Taylor series with remainder term and then use these to illustrate numerical convergence in the approximation of the sine function. Along the way we introduce the important concept of numerical error and point out that it is a combination of both truncation error and round-off error. In order to be able to discuss the relationship of numerical error to the number of significant figures in our approximations we introduce the concepts of true error and computational error, and show how they can are related to significant figures by the approximate expression

1 We define the normalized computational error by the rubric

38

and show that this approach leads to a satisfactory approximation of the number of significant figures obtained with evaluating the value of when converging the Maclaurin sine series by comparing Taylor sine polynomials of increasing degree. This works well as long as our denominator is not converging on zero. In the process of using the sine series to illustrate convergence and significant figures we guide the student through a tutorial for Excel and Visual Basic to create user defined functions that can be used in an Excel spreadsheet to implement a numerical method. This requires us to spend some time on the tedious issue of macro security and includes a discussion of how to modify the default security settings in Excel to allow us to run our custom functions written in VB. We use Excel tutorials in the chapter to introduce several of the most important programming concepts needed to write the computer code we use in the rest of the text to implement our numerical methods. Thus we spend some time understanding fixed loops, specifically the For...Next statement, and conditional loops, in particular the While...Wend statement. We also introduced the powerful and necessary concept of dimensioned variables and discussed how to create dimensioned variables in a VB program using the Dim statement, in order to be able to return multiple variable values to the spreadsheet. Finally, we end the chapter with an unpleasant but still necessary discussion of the problem of locating and correcting errors in the VB code we will write for our user defined functions. This discussion includes both the need for locating the Reset button in the VB editor for the inevitable occasion of compiler errors, and the recommendation of the use of the MsgBox command for isolating and diagnosing our inevitable programming mistakes that are not found until the code begins to execute.

Exercises 1. For small values of, the approximation is often used. Find the error in using

this formula based on Taylor's theorem. For what values of will the formula give results correct to 6 significant figures? For what values of will the formula be correct to 3 significant figures?

2. For small values of what range of values will be correct to 4 significant figures if we use the approximationcos 1 ?

3. Show that every polynomial of any degree is its own Taylor series expansion. 4. Derive the MacLaurin Series expansion for where:

a. b. c. d. e. f. 1 g. h. 1

39

i. , where

5. Using an Excel spreadsheet, compare the values of the first five Taylor polynomials obtained from the Maclaurin series for one of the functions in Exercise 4 and determine the number of significant figures at 0.1, 0.01, and 0.001. Discuss how your results illustrate the concept of convergence in a numerical approximation.

6. For the Maclaurin series of one of the functions in Exercise 4 evaluated at 0.1 show that the truncation error for the 5th degree Taylor polynomial is less than the maximum value of the remainder term from Equation (1.12).

7. The user defined function TSE_sin_sf(x, sf) developed in Example 1.2 and shown in Figure 1.18 fails to converge the Maclaurin sine series to the correct value of when 0, despite that fact that at 0 every term in the series is exactly zero. This is easily verified when using the spreadsheet shown in Figures 1.14 by setting 0 in cell A2. Using the MsgBox command, determine where in the code for TSE_sin_sf(x, sf) the error is occurring and explain the nature of the error. Can you suggest a modification of the code that would prevent this error? Hint: In the VB editor window type If...Then...Else in the help search box.

8. For on the closed interval /2, /2: a. Use Excel to plot the function on the indicated interval. b. Use the Excel VB editor to write a user defined function that will calculate the term

in the Maclaurin series expansion for for given values of and . Show your code. c. Use the VB function from part b to estimate at 1.0 using only the first 4 terms

in the series. Show the calculations on a spreadsheet. d. In your own words give definitions of the true error, the normalized true error, the

computational error, and the normalized computational error. e. In using a numerical method to approximate the solution to an equation, why do we have

to depend on the normalized computational error to decide when our approximation is adequate?

f. What is the relationship between normalized computational error and significant figures? g. Compare 1.0 estimated from the Maclaurin series and the value of 1.0 given by

the Excel built-in function. How many terms in the expansion are required to get 1.0 correct to 8 significant figures based on the normalized true error? Assume that the Excel built-in function gives you the true value for 1.0.

h. How many terms are required to get 1.0 correct to 8 significant figures based on the normalized computational error?

i. How many terms are required to get 0.5 correct to 8 significant figures based on the normalized true error? Based on the normalized computational error?

j. Develop a VB function that will use the Maclaurin series with a While...Wend loop to estimate at any value of within the radius of convergence of the expansion and to a specified number of significant figures of accuracy. The module should return both the estimate of and the number of terms in the approximation. Show your code.

k. Accordin