## ASCII Codes

The following table lists the ASCII codes for some useful non-printing keyboard characters (codes 8, 9, 10, 13, 27), the keyboard characters (codes 32127) and the alternate character set (codes 128-255). The alternate characters can be printed by holding down the ALT key while typing 0 , e.g., for , type ALT+0177. 8 backspace 10 line feed 27 escape 9 horizontal tab 13 carriage return

## Using the Regression Tool in Analysis Tools

Linear regression can also be performed using the Add-In package called the Analysis ToolPak. If the Analysis ToolPak Add-In is installed, the Data Analysis command will be present at the bottom of the Tools menu if the Data Analysis command is not present in the Tools menu, choose Add-Ins from the Tools menu and check the box for Analysis ToolPak or Analysis ToolPak (VBA) to install it. Now when you click on the Tools menu you will see the Data Analysis command. Figure 13-14. The Data Analysis...

## The Visual Basic Editor

To create VBA code, or to examine existing code, you will need to use the Visual Basic Editor. To access the Visual Basic Editor, choose Macro from the Tools menu and then Visual Basic Editor from the submenu. The Visual Basic Editor screen usually contains three important windows the Project Explorer window, the Properties window and the Code window, as shown in Figure 1-1. (What you see may not look exactly like this.) The Code window displays the active module sheet each module sheet can...

## One Dimensional Array Assigned to a Worksheet Range Can Cause Problems

Arrays can cause some confusion when you write the array back to a worksheet by assigning the value of the array to a worksheet range. VBA considers a one-dimensional array to have the elements of the array in a row. This can cause problems when you select a range of cells in a column and assign an array to it, as in the following Range(E1 E10). Value TestArray The preceding statement causes the same value, the first element of the array, to be entered in all cells in the column. However, if...

## Linear Interpolation in a Table by Means of a Custom Function

The linear interpolation formula can also be easily coded as a custom function, as shown in Figure 5-10. Function lnterpL(lookup_value, known_x's, known_y's) Dim pointer As Integer Dim XO As Double, Y0 As Double, X1 As Double, Y1 As Double pointer Application.Match(lookup_value, known_x's, 1) XO known_x's(pointer) YO known_y's(pointer) X1 known_x's(pointer + 1) Y1 known_y's(pointer + 1) InterpL YO + (lookup_value - XO) * (Y1 - YO) (X1 - XO) Figure 5-10. Function procedure for linear...

## Problems

Data for, and answers to, the following problems are found in the folder Ch. 15 (Random Numbers & Monte Carlo) in the Problems & Solutions folder on the CD. 1. Estimation of 71. The equation of a circle is x2 + y2 r2. Evaluate n by determining the area of a circle of radius r circumscribed by a square of side 2r. 7i is the ratio of the area of the circle to that of the square. Generate a pair of random numbers to use as the * and y coordinates. If the distance of the point from the origin...

## The Objective of This Book

Numerical methods require extensive calculation, which is easily accomplished using today's desktop computers. A number of books have been written in which numerical methods are implemented using a specific programming language, such as FORTRAN or C++. Most scientists and engineers received some training in computer programming in their college days, but they (or their computer) may no longer have the capability to write or run programs in, for example, FORTRAN. This book shows how to implement...

## Solving Hyperbolic Partial Differential Equations Replacing Derivatives with Finite Differences

Once again, we can solve the problem by replacing derivatives by finite differences. which, when rearranged, yields Tg (At)2 If we set Tg(Atflw(Ax)2 1, equation 12-33 is simplified to equation 12-34. Interestingly, this simplified expression also yields the most accurate results. When employing the simplified equation, the value of At is determined by the expression Equation 12-34 calculates the value of the function at time tn from values at t and L . Figure 12-13 shows the stencil of the...

## Some Notes on the Solver Parameters Dialog

There are some additional controls in the Solver Parameters dialog box By Changing Cells. You can use names instead of cell references for individual cells or ranges in the By Changing Cells input box. For ease of editing an extensive series of references in the By Changing Cells input box, press F2 you can then use the arrow keys to move within the box. Constraints. With the Solver you can apply constraints to the solution. For example, you can specify that a parameter must be greater than or...

## Using Array Constants to Create Series Formulas

An array constant is an array of values, separated by commas and enclosed in braces, used as an argument of a function. An example of an array constant, sometimes referred to as an array literal, is 40,21,300,10 . You can use an array constant to make the evaluation of a series formula much more compact and accurate. For example, to evaluate equation 4-1, the formula returns the value 2.718 281 801 146 38 (1 x KT6 error).

## Chapter Random Numbers and Monte Carlo

The answer spreadsheet contains two examples. The first uses 32 points, and is intended mainly to illustrate the method. Random number formulas are used to generate a pair of x, y coordinates in columns A and B. The formula in column C uses an IF statement to determine whether the point is inside the circle if inside, the formula returns the y coordinate, otherwise the cell is blank. The second example uses 4000 points and is used to create the chart. The formula in column G returns the y...

## Using Random Numbers in Excel

You can use random numbers in many ways, for example to add noise to a signal generated by a formula, to select items randomly from a list, or to perform a simulation by using the Monte Carlo method. These and some other uses of random numbers will be described in following sections. Excel provides several ways to generate random numbers. The worksheet function RAND returns a random real number greater than or equal to 0 and less than 1. RAND is a volatile function that is, a new random number...

## Importing Trendline Coefficients into a Spreadsheet by Using Worksheet Formulas

The following are the various Trendline fitting functions that are displayed in the Add Trendline dialog box directly into worksheet cells The linear, logarithmic and polynomial expressions are linear in the coefficients and can be handled by Excel's built-in linear regression code. Trendline uses linear transformation of the power and exponential functions to obtain the coefficients the exponential expression is transformed to ln(y) bx + ln(a) and the power expression to ln(y) b ln(jc) +...

## Engineering and Other Functions

The following functions are available only if you have loaded the Analysis ToolPak. Most are listed in the Engineering category in the Insert Function dialog box. Returns the modified Bessel function Jn(x) Returns the modified Bessel function Kn(x) Converts a binary number to hexadecimal Converts real and imaginary coefficients into a complex number Converts a number from one measurement system to another Converts a decimal number to hexadecimal Returns the serial number of the date that is a...

## Handling Noncontiguous Ranges of knownxs in LINEST

One of the few limitations of LINEST is that the range of known_x's must be a contiguous selection (e.g., A 2 C 13 in Figure 13-9). Occasionally, you may wish to perform multiple linear regression where the known_x's are not in adjacent rows, and it may not be convenient to rearrange the spreadsheet so as to obtain a contiguous range of known_x's. You can use the custom function Arr to combine separate ranges into a single array. For example, if the ranges of independent variables x , x2 and x3...

## Using the ROW Worksheet Function to Create Series Formulas

The ROW worksheet function provides a convenient way to generate a series of integers. To illustrate the use of this function in a formula, enter the formula ROW(1 100) in a worksheet cell. Now highlight the formula in the formula bar or in the cell and press F9 (Windows) or COMMAND+ (Macintosh) to display the result of the formula. You will see the array of integers from 1 to 100, as shown below. 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 Using this method...

## Creating a Custom Lookup Formula to Obtain Values from a Table

A second way to lookup to the left in a table is to construct your own lookup formula using Excel's MATCH and INDEX worksheet functions. The MATCH and INDEX functions are almost mirror images of one another MATCH looks up a value in an array and returns its numerical position, INDEX looks in an array and returns a value from a specified numerical position. The following example illustrates how to use INDEX and MATCH to lookup to the left in a table. In the table of production figures for...

## A B j jDJ

1 Modified formula improves convergence Figure 8-9. Modifying the Regula Falsi method to handle slow convergence, (folder 'Chapter 08 Examples', workbook 'Roots of Equations', worksheet 'Regula Falsi (3)') Figure 8-9. Modifying the Regula Falsi method to handle slow convergence, (folder 'Chapter 08 Examples', workbook 'Roots of Equations', worksheet 'Regula Falsi (3)') The preceding methods require manual selection of a pair of starting values with opposite signs. The Newton-Raphson method...

## How Excel Generates Random Numbers

In Excel 2003, an improved random number generator was implemented. Earlier versions of Excel used a pseudo-random-number-generation algorithm whose performance on standard tests of randomness was not sufficient to satisfy the demand of power users who might require the generation of a million or more random numbers. For the majority of users, the older pseudo-random-number generator was satisfactory. The earlier algorithm used the following iterative method to calculate pseudo-random numbers r...

## Objects Properties and Methods

VBA is an object-oriented programming language. Objects in Microsoft Excel are the familiar components of Excel, such as a worksheet, a chart, a toolbar, or a range. Objects have properties and methods associated with them. Objects are the nouns of the VBA language, properties are the adjectives that modify the nouns and methods are the verbs (the action words). Objects are used almost exclusively in Sub procedures, while properties and some methods can be used in Function procedures. A...

## Vapor Diffusion in a Tube Again

This example, using the same data, illustrates the use of a smaller grid size. The spreadsheet 'Crank-Nicholson 3' , not shown here, can be examined on the accompanying CD-ROM. The x-increment is 2 cm, thus creating a table of values that is 11 columns wide, including the boundary values, and requiring a 9 x 9 matrix of coefficients. The spreadsheet employs a single formula for all cells of the coefficients table The formula uses the following named formulas CoeffTableCol COLUMN -MIN COLUMN...

## Eulers Method

Let us use in our first calculation an example of equation 10-1 the first-order kinetic process A B with initial concentration Co 0.2000 mol L and rate constant k 5 x 10 3 s '. We'll simulate the change in concentration of the species A vs. time over the interval from t 0 to t - 600 seconds, in increments of 20 seconds. The differential equation for the change in concentration of the species A as a function of time is Expressing this in terms of finite differences, the change in concentration A...

## Vba Runge Kutta Excel

Performs fourth-order Runge-Kutta integration of a system of N ordinary differential equations. Returns the values of the N independent variables y at x Ax, based on specified values of x and the N independent variables y at x, and N differential equations. Runge3 x_variable, y_variables, deriv_formulas, interval, index the array of differential equations dy dx F x_variable, y_variable , in the same order as y_variabies Ax, the interval for the calculation an optional argument specifying which...

## A Linest Shortcut

Here's a shortcut that eliminates the need to create the columns of W2 and W3 in Figure 13-10. If you've read Chapter 4, Number Series, and understand array constants, you'll understand how the formula creates an array of the values of the independent variable W raised to the first, second and third powers. Unlike the braces that are automatically placed around an array formula when you enter it by using CONTROL SHIFT ENTER, you must type the braces around the values of the array constant. You...

## Limitations of the Regression Tool

Unlike Trendline, the Regression tool in Data Analysis the Analysis Toolpak provides the coefficients and statistical parameters of linear regression as values in cells, ready to be used in calculations. And, they are presented in a nicely formatted table. The major limitation of the regression tool is that, unlike LINEST, it is not a function. With LINEST, the returned values are dynamically linked to the original data and are updated if the raw data is changed. If you use the Regression tool,...

## Some Useful Methods

Methods can operate on an object or on a property of an object. Some methods that can be applied to the Range object are the Copy method, the Cut method, the FillDown method or the Sort method. Statements involving methods usually do not appear in an assignment statement that is, no equal sign is required . For example, clears the formulas and formatting in the range A1 E1. Some useful VBA methods are listed in Table 2-7. Copies an object to a specified range or to the Clipboard. Cuts an object...

## Returning an Array of Values as a Result

The most obvious way to enable a Function procedure to return an array of values is to assemble the values in an array and return the array. The procedure shown in Figure 2-19 illustrates a function that returns an array of three values. To use the function, the user must select a horizontal range of three cells, enter the function and press CONTROL SHIFT ENTER. Function MyLINEST known_ys, known_xs Dim Results 3 code to calculate slope, intercept and R-squared Results 1 MySlope Results 2...

## Alternating Series Function On

ABS worksheet function 256, 279 active cell, reference to 35 add a breakpoint 55, 56 add a shortcut key 15 Add Trendline 298, 299 Add Watch 55,57 Add, Change, Delete Solver parameters 324 Add-In function macros 53 Add-In macro, create an 53 Add-Ins 303 Addlns folder 53 addition, matrix 58 additional matrix functions 63 Address property 116 alternating series 69 Analysis ToolPak 289, 303, 343, 347, 425 And keyword 17, 25 approximation error 110 area between two curves 13 2 of an ellipse 144 of...

## Msg Box Return Values

MsgBox can return a value that indicates which button was pressed. This allows you to take different actions depending on whether the user pressed the Yes, No or Cancel buttons, for example. To get the return value of the message box, use an expression like ButtonValue MsgBox prompt_text, buttons, title_text, helpfile, context Note that the arguments of MsgBox must be enclosed in parentheses in order for it to return a value. The return values of the buttons are as follows OK, 1 Cancel, 2...

## Working with Arrays in Sub Procedures Passing Values from Worksheet to VBA Module

There are two ways to get values from a worksheet into a VBA array. You can either set up a loop to read the value of each worksheet cell and store the value in the appropriate element of an array, or you can assign the VBA array to a worksheet range. The former method is straightforward the latter method is described in the following section. Depending on which of these two methods you use, there can be a definite difference with respect to execution speed that could become important if you...

## Arguments with or without Parentheses

The arguments of a method sometimes appear within parentheses, sometimes without parentheses see the examples immediately preceding . Sometimes either syntax will work, sometimes one or the other fails. Why is this As well as performing an action, methods create a return value. The return value can be either True or False True means the method worked, False means that it failed. Even the ChartWizard method creates a return value True if the chart was created successfully, False if the method...

## Chapter Nonlinear Regression and the Solver

Enter formula for Acalc you'll need a cell for k, the changing cell . Enter formula for residual 2 and sum the squares of residuals this is the target cell . Use the Solver to minimize the target. Answer k 0.3290. 2. Follow the same procedure as in problem 1. Answer a 0.5005. 3. Follow the same procedure as in problem 1, except that there are four changing cells. Answer a 1.0644246, b 1.8495246, c -0.8966248, d 9.97124864. 4. The answer spreadsheet has been set up with headings for using the...

## Solving a Second Order Ordinary Differential Equation by the Shooting Method and Eulers Method

Consider an unknown function y F x that obeys the second-order differential equation y -y 0 and that is known to have boundary values of y 0 at x 0 and y 3.63 at x 2. To solve the second-order differential equation we express it as two first-order differential equations The initial calculation, using a trial value of z 0, is shown in Figure 11-6. Figure 11-6. Preparing to solve the differential equationy -y 0 by the shooting method. The initial boundary values and the initial trial value of the...

## Chapter Roots of Equations

To find the first time after t 0 when the current reaches zero, you must begin with a value of t that will force Goal Seek to converge to the first i 0 after t 0. Using t 1 is a good choice, t 1.576 seconds. 3. The spreadsheet shows a manual method, similar to the interval-halving method, and also uses Goal Seek. Ba2 1.28 x 105 M. 4. The spreadsheet shows the graphical method and also uses Goal Seek. S 0.13 mol L. 5. Use Goal Seek with Y1-Y2 as target cell formula Use two different initial...

## Solving a Second Order Ordinary Differential Equation by the Shooting Method and the RK Method

Using the Runge-Kutta method should produce much smaller errors than does Euler's method. Figure 11-8 shows the application of the RK method to the preceding problem, the solution of the differential equation y - y 0. Four columns, B F, were inserted and labeled TZ1 TZ4, for the four RK terms used to calculate z. Similarly, four columns were inserted for the calculation of y. As in Figure 11-7, the values in bold are the two boundary values in cells G6 and L6 and the target value cell L34 ....

## Appendix Selected VBA Keywords

This listing of VBA objects, properties, methods, functions and other keywords will be useful when creating your own VBA procedures. The list is not exhaustive, but contains mainly those keywords that are used in the procedures shown in this book. For each VBA keyword, the required syntax is given, along with some comments on the required and optional arguments, one or more examples and a list of related keywords. See Excel's On-Line Help for further information. Returns the absolute value of a...

## Summary of Contents

Detailed Table of Preface About the Chapter 1 Introducing Visual Basic for Applications Chapter 2 Fundamentals of Programming with VBA Chapter 3 Worksheet Functions for Working with Matrices 57 Chapter 4 Number Series Chapter 5 Interpolation Chapter 6 Differentiation Chapter 7 Integration Chapter 8 Roots of Equations Chapter 9 Systems of Simultaneous Equations Chapter 10 Numerical Integration of Ordinary Differential Equations Part I Initial Conditions Chapter 11 Numerical Integration of...

## Least Squares Fit to a Straight Line Using LINEST

Although you may find LINEST a bit confusing at first the help description for most functions occupies a page or less, while the printed help for LINEST is seven pages , you will soon get the hang of it and will find that it is much to be preferred over the other methods that Excel provides for doing least-squares curve fitting. The general form of the linear equation that can be handled by LINEST is y-m1xi m2x2 m3x3 b 13-12 LINEST returns the array of regression coefficients mn, , m2, mu b....

## An Example Temperature Distribution in a Heated Metal Plate

A typical example of an elliptic partial differential equation involves the solution of a steady-state heat-flow problem. For example, if a thin steel plate, 10x10 cm, has one of the edges held at 100 C and the other three edges at 0 C, what are the steady-state temperatures within the plate For simplicity, we assume that heat is not lost through the faces of the plate. We subdivide the plate by means of a grid with h k 0.5 cm, thus creating a lattice of size 20 x 20. At equilibrium, heat flows...

## Fourth Order Runge Kutta Method Applied to a Differential Equation Involving Both x and y

In the preceding examples, the differential equation involved only the dependent variable y. In the general case, the differential equation can be a function of both x and y. The following example illustrates the use of the Runge-Kutta method for dy dx F x, y . A function is described by the differential equation and the function has the value y 0.5 at x 0. We want to find the value of the function over the range x 0 to x 1. Figure 10-4 illustrates the use of the RK method to model the...

## Solving a Second Order Ordinary Differential Equation by the Finite Difference Method Another Example

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. Oenerai formula y ay- bx c Figure 11-13. Portion of the spreadsheet to solve the...

## Excel for Scientists and Engineers

WILEY-INTERSCIENCE A John Wiley amp Sons, Inc., Publication Excel for Scientists and Engineers The Wiley Bicentennial-Knowledge for generations r ach generation has its unique needs and aspirations. When Charles Wiley first opened his small printing shop in lower Manhattan in 1807, it was a generation of boundless potential searching for an identity. And we were there, helping to define a new American literary tradition. Over half a century later, in the midst of the Second Industrial...

## Msg Box

The MsgBox dialog box allows you to display a message, such as Please wait or Access denied. The box can display one of four message icons, and there are many possibilities in the number and function of buttons that can be displayed. The syntax of the MsgBox function is MsgBox prompt_text, buttons, title_text, helpfile, context where prompt_text is the message displayed within the box, buttons specifies the buttons to be displayed, and title_text is the title to be displayed in the Title Bar of...

## Nonlinear Regression Using the Solver An Example

To perform nonlinear least-squares curve fitting using the Solver, your spreadsheet model must contain a column of known y values and a column of calculated y values, so that the sum of squares of residuals can be calculated. The calculated y values must be spreadsheet formulas that depend on the curve fitting coefficients that will be varied by the Solver. To illustrate the use of the Solver for nonlinear least-squares curve fitting, we'll use as an example the system of two consecutive...

## Entering VBA Code

Of course, most of the VBA code you create will not be recorded, but instead entered at the keyboard. As you type your VBA code, the Visual Basic Editor checks each line for syntax errors. A line that contains one or more errors will be displayed in red, the default color for errors. Variables usually appear in black. Other colors are also used comments see later are usually green and some VBA keywords Function, Range, etc. usually appear in blue. These default colors can be changed if you...

## Stepping Through Code

There are two ways to begin the process of stepping through the code of a Sub procedure Select the name of the procedure in the Macro Name list box and press the Step Into button. This will display the code module containing the procedure the first line of the procedure will be highlighted in yellow, as in Figure 2-22 . Add a breakpoint as described in the following section, then run the Sub procedure in the usual way. When the code window is displayed, with a line of code highlighted, you can...

## An Example Vapor Diffusion in a Tube

An air-filled tube 20 cm long allows water vapor to diffuse from a source liquid water to a drying chamber, where the vapors are dissipated. Initially the tube is capped so that the vapor cannot escape. The temperature of the tube is held at 30 C. The equilibrium vapor pressure of water at this temperature is 31.8 mm Hg thus the vapor pressure inside the tube is 31.8 mm Hg. When the cap is removed, the vapor will diffuse toward the drying chamber, where the water vapor pressure is zero. We wish...

## Two Ways to Specify Arguments of Methods

VBA methods usually take one or more arguments. The Sort method, for example, takes 10 arguments. The syntax of the Sort method is object.Sort key1, orderl, key2, order2, key3, order3, header, orderCustom, The object argument is required all other arguments are optional. You can specify the arguments of a method in two ways. One way is to list the arguments in order as they are specified in the preceding syntax, i.e., Range A1 E150 .Sort Last Name, xIAscending which sorts the data contained in...

## An Example Vibration of a String

A string 50 cm long and weighing 0.5 g is under a tension of 33 kg. Initially the mid-point of the string is displaced 0.5 cm from its equilibrium position and released. We want to calculate the displacement as a function of time at 5 cm intervals along the length of the string, using equation 12-34. From equation 1235 the At must be 8.8 x 10 5 seconds. The spreadsheet shown in Figure 12-14 illustrates the solution of the vibrating string problem. Column B contains time in increments of At from...

## Linear Regression

Linear regression is not limited to the case of finding the least-squares slope and intercept of a straight line. Linear regression methods can be applied to any function that is linear in the coefficients . Many functions that produce curved x-y plots are linear in the coefficients, including power series, for example, and some functions containing exponentials, such as Mathematically, a function that is linear in the coefficients is one for which the partial derivatives of the function with...

## Derivatives of a Worksheet Formula

Instead of calculating the first or second derivative of a curve represented by data points, we may wish to find the derivative of a function a worksheet formula . In the following, two different methods are illustrated to calculate the first or second derivative of a worksheet formula by using a user-defined function. The calculation of the first derivative of the function y 3x3 5x2 - 5x 11 is used as the example for each method Derivatives of a Worksheet Formula Calculated by Using a VBA...

## An Example Heat Conduction in a Brass

Consider an insulated 10-cm brass rod, initially at a temperature of 0 C. One end of the rod is heated to 100 C. Equation 12-20 describes the heat flow in the rod as a function of time. For simplicity, we assume that there is no heat loss through the sides of the rod. For brass, the coefficient of thermal conductivity k is 0.26 cal s cm-1 deg ', the heat capacity c is 0.094 cal g_1 deg-1 and the density p is 8.4 g cm-3. From these values, the coefficient k in equation 12-22 is 3.04 s cm2....

## Nonlinear Regression Using the Solver

If you have read the preceding chapter on linear regression and are familiar with the use of LIN EST, you should have no trouble recognizing a function that is linear in the coefficients. Some examples of functions that are linear in the coefficients arsy a bx cx2 dx ory aex. However, if the function is one such as it is not linear in the coefficients. It should be obvious that it's not possible to apply LI NEST to this equation given a column of x values, you can't create a column of ea bx...

## Some Additional Matrix Functions

Some additional functions useful for working with arrays or matrices are provided on the CD that accompanies this book. The additional functions are as follows Identity Matrix. The function MIDENT s 'ze returns an identity matrix of a specified size. The size argument is optional. Use size when you want to use an identity matrix in a formula. Omit size when you want to fill a range of cells on a worksheet with an identity matrix the size of the matrix is then determined by the size of the...

## The Gauss Seidel Method Implemented on a Worksheet Using Circular References

The worksheet in the preceding section can be easily modified to use intentional circular references, as follows. After entering the starting values in row 13 and the formulas in row 14 as before Figure 9-11 , change the cell references in the formulas in cells B14 and C14 from references to row 13 to references to row 14. The formulas in cells B14, C14 and D14 are now, respectively, E 8- C 8 C14- D 8 D14 B 8 E 9- B 9 B14- D 9 D14 C 9 E 10- B 10 B14- C 10 C14 D 10 This produces the Cannot...

## Integrating a Function

Instead of finding the area under a curve defined by a set of data points, you may wish to integrate a function F x . You could simply create a table of function values and use one of the methods described in earlier sections to calculate the area. But a more convenient solution would be to create a custom function that uses the Formula property of the cell to get the worksheet formula to be integrated, in the same way that was used in the preceding chapter, and uses the formula to find the...

## Least Squares Fit to a Straight Line Using the Worksheet Functions Slope Intercept and RSQ

Figure 13-1 shows the phase diagram of methane hydrate, one of a class of compounds known as clathrate hydrates. Methane hydrate, an ice-like solid, consists of methane molecules trapped in a crystalline lattice of water molecules each unit cell of the crystal lattice contains 46 water molecules and up to 8 gas molecules. The figure shows that the solid phase forms under conditions of high pressure and relatively low temperature. Previously, information about the formation of methane hydrate...

## Logarithmic

The curve follows equation A4-7 with a - 2, b 1. Data with the behavior shown in Figure A4-9 can be fitted by the logarithmic equation A4-7. 'Plateau Curve. A relationship of the form exhibits the behavior shown in Figure A4-10. 1 Figure A4-10. Plateau curve. The curve follows equation A4-8 with a ,b . In biochemistry, this type of curve is encountered in a plot of reaction rate of an enzyme-catalyzed reaction of a substrate as a function of the concentration...

## Area under a Curve

By area under a curve we mean the area bounded by a curve and the x-axis the line y 0 , between specified limits. The area can be positive if the curve lies above the x-axis or negative if it is below. Calculation of the area under a curve is sometimes referred to as quadrature, since it involves subdividing the area under the curve into a number of panels whose areas can be calculated. The sum of the areas of the panels will be an approximation to the area under the curve. The three most...

## Cubic Interpolation in a Two Way Table by Means of Worksheet Formulas

To perform cubic interpolation between data points in a two-way table, we use a procedure similar to the one for linear interpolation. Figure 5-18 shows the table of viscosities that was used earlier. In this example we want to obtain the viscosity of a 63 solution at 55 F. The shaded cells are the values that bracket the desired x and y values. Viscosity of Heat Transfer Fluid cps Figure 5-18. Cubic interpolation in a two-way table. The shaded cells are the ones used in the interpolation,...

## Importing the Trendline Equation from a Chart into a Worksheet

Scientists and engineers often use Excel's Trendline feature to obtain a least-squares fit to data in a chart. Trendline provides a limited gallery of mathematical fitting functions, including regular polynomials up to order six. The disadvantage of Trendline is that the trendline equation is merely a caption in the chart to use it in the worksheet, the coefficients must be transferred manually by typing, or copying and pasting. The utility TrendlineToCell provided on the CD-ROM converts the...

## Solving Parabolic Partial Differential Equations The Crank Nicholson or Implicit Method

In the explicit method, we used a central difference formula for the second derivative and a forward difference formula for the first derivative equations 1224 and 12-25 . A variant of equation 12-26 that makes the approximations to both derivatives central differences is known as the Crank-Nicholson formula - rF,_hj 2 2r FiJ l - rFMJ x rF j 2 - 2r Fu rFMJ or, if i represents distance x and j represents time t, rFx_u x 2 2r Fx l l - rFx J l rFx_u 2 - 2r Fxt rFx X t where r Ay k Ax 2 . Choosing...

## Branching

VBA supports If Then statements very similar to the Excel worksheet function IF. The syntax of lf Then is If LogicalExpression Then statementl Else statement2 The lf Then statement can be a Simple If statement, for example If x gt 0 Then numerator 10 A x If LogicalExpression in this example x gt 0 is True, statementl is carried out if LogicalExpression is False, nothing is done program execution moves to the next line . lf Then Else structures are also possible. For example If Err.Number 13...

## Simple Predictor Corrector Method Utilizing an Intentional Circular Reference

An intentional circular reference can be used in the corrector formula to eliminate the need to Fill Right the corrector formula in order to perform the iterations. The corrector formula in cell C6 is changed from the formula shown above to which creates a circular reference, since cell C6 refers to itself. A circular reference is usually an error Excel displays the Cannot resolve circular references error message and puts a zero in the cell. In this case, however, the circular reference is...

## Some Notes on the Solver Options Dialog

The Options button in the Solver Parameters dialog box displays the Solver Options dialog box Figure 14-7 and allows you to control the way Solver attempts to reach a solution. The default values of the options are shown in Figure 14-7. Max Time and Iterations. The Max Time and Iterations parameters determine when the Solver will return a solution or halt. If either Max Time or Iterations is exceeded before a solution has been reached, the Solver will pause and ask if you want to continue. For...

## Evaluating Series Formulas

The obvious way to evaluate a series formula is to evaluate individual terms in the series formula in separate rows of the spreadsheet, and then sum the terms. Figure 4-1 illustrates the evaluation of e by using equation 4-1, summing terms until the contribution from the next term in the series is less than 1E-15. Figure 4-1. Evaluation of the terms of a series row-by-row. The spreadsheet calculates the value of e by using equation 4-1 . Note that some rows of calculation have been hidden....

## Logistic Curve with Offset on the Axis The logistic equation

Logistic curve with offset on they-axis. The curve follows equation A4-13 with a 1, b -2, c 1 and d -0.2. This equation takes into account the value of the plateau maximum and minimum coefficients a and d, respectively , the offset on the -axis, and the Hill slope. Gaussian Curve. The Gaussian or normal error curve equation A4-14 y _ exp - x - m 2 j2 A4_H cr Jln can be used to model UV-visible band shapes, usually in order to deconvolute a spectrum consisting of two or more...

## Simple Predictor Corrector Method

To illustrate the method we will modify the simple Euler method, equation 10-6, as follows. The predictor equation is yn i jvi 2hF x ,y 10-28 which requires values at x _i and x to calculate _y i- Once we have an approximate value for y i, we use the corrector equation to get an improved value of y i. The corrector equation is used iteratively the value of y i is used to obtain an improved value of y i and the process is continued until a specified level of convergence is obtained. Two starting...

## Assigning a Shortcut Key to a Sub Procedure

If you didn't assign a shortcut key to the macro when you recorded it, but would like to do so after the fact, choose Macro from the Tools menu and Macros from the submenu. Highlight the name of the macro in the Macro Name list box, and press the Options button. You can now enter a letter for the shortcut key CONTROL lt key gt or SHIFT CONTROL lt key gt in Excel for Macro recorded 6 22 2004 by Boston Coflege Windows, OPTION COMMAND lt key gt or SHIFT OPTION COMMAND lt key gt in Excel for the...

## Differential Equations

For a function F x,y that depends on more than one independent variable, the partial derivative of the function with respect to a particular variable is the derivative of the function with respect to that variable while holding the other variables constant. For a function of two independent variables x and y, the partial derivatives are dF x,y dx y held constant and dF x,y idy x held constant . There are three second-order partial derivatives for the function F x,y amp F x,y ldx, amp F x,y...

## Multiple Linear Regression Using LINEST

Now that we've gained some familiarity with LINEST, let's apply it to an example of multiple linear regression. The data table in Figure 13-7 lists the freezing points of solutions of ethylene glycol. We want to be able to obtain the freezing point of a solution of ethylene glycol with wt that is intermediate between the data values given in the table. Figure 13-7. Freezing point of ethylene glycol-water solutions, folder 'Chapter 13 Examples', workbook 'Dowtherm data', sheet 'Using Trendline'...

## Fourth Order Runge Kutta Method Implemented on a Worksheet

The spreadsheet in Figure 10-2 illustrates the use of the RK method to simulate the first-order kinetic process A gt B, again using initial concentration A 0 0.2000 and rate constant k 5 x 10 3. The differential equation is, again, equation 10-4. This equation is of the simple form dy dx F y , and thus only the yi terms of T to T4 need to be evaluated. The RK terms note that T is the Euler method term are shown in equations 10-12 through 10-15. Figure 10-2. Simulation of first-order kinetics by...

## Predictor Corrector Methods

The methods in the preceding sections are one-step methods. They need only the value of the preceding point to calculate the value of the new point. Thus they are self-starting methods. Predictor-corrector methods, on the other hand, use the values of two or more previous points to calculate the value of the new point. They are not self-starting two or more known initial values are needed. Often a Runge-Kutta calculation is used to provide the needed values. Predictor-corrector methods use two...

## The Shooting Method

The shooting method is a trial-and-error method. To solve a problem where the values of y are known at x0 and x , the boundaries of the interval of interest, we set up the problem as though it were an initial-value problem, with two knowns given at the same boundary for example, at x0- See Figure 10-17 for an example of an initial-value problem of this type the two knowns, shown in bold, are the value ofy at jc0 and a trial value of y' at jc0. Using the trial value of y we calculate y for a...

## Gauss Seidel

Solves a set of N linear equations in N unknowns by the Gaussian-Seidel method. Returns the array of N unknowns, in column format only. init_values coeff_matrix a reference to an N row x N column array of coefficients const_vector a reference to an N row x 1 column array of constants init_values a reference to an N row x 1 column array of initial values The coeff_matrix, const_vector and init_values tables can contain values or formulas. The optional init_values may be helpful for large arrays....

## Higher Order Differential Equations

Differential equations of higher order can also be solved using the methods described in this chapter, since a differential equation of order n can be converted into a set of n first-order differential equations. For example, consider the following second-order differential equation equation 10-30 that describes the damped vibration of a mass m connected to a rigid support by a linear spring with coefficient ks and a vibration damper with coefficient kd, illustrated in Figure 1015. Figure...

## The Personal Macro Workbook

The Record Macro dialog box allows you to choose where the recorded macro will be stored. There are three possibilities in the Store Macro In list box This Workbook, New Workbook and Personal Macro Workbook. The Personal Macro Workbook PERSONAL.XLS in Excel for Windows, or Personal Macro Workbook in Excel for the Macintosh is a workbook that is automatically opened when you start Excel. Since only macros in open workbooks are available for use, the Personal Macro Workbook is the ideal location...

## Numerical Integration of Ordinary Differential Equations Part I Initial Conditions

A differential equation is an equation that involves one or more derivatives. Many physical problems, when formulated mathematically, lead to differential equations. For example, the equation k gt 0 describing the decrease in y as a function of time, occurs in the fields of reaction kinetics, radiochemistry or electrical engineering where y represents concentration of a chemical species, or atoms of a radioactive element, or electrical charge, respectively as well as in many other fields. Of...

## SimultEqNL

Solves a set of N non-linear equations in N unknowns by Newton's iteration method. Returns the array of N unknowns, in column format only. SimultEqNL e lt 7 at o gt s, variables, constants equations a reference to an N row x N column array of coefficients variables a reference to an N row x 1 column array of constants constants a reference to an N row x l column array of initial values The coeff__matrix, const_vector and init__vaiues tables can contain values or formulas. The optional...

## Dimensioning an Array

The Dim short for Dimension statement is used to declare the size of an array. Unless specified otherwise, VBA arrays begin with an index of 0. Thus the statement establishes array storage for 11 elements, Sample O through Sample 10 . However, you can specify that the arrays in your procedure begin with an array index of 1. Since worksheet ranges, worksheet functions and worksheet arrays use or assume a lower array index of 1, always specifying VBA arrays with lower array index of 1 can...

## The Contents of the CD

The CD-ROM that accompanies this book contains a number of folders or other documents an Examples folder. The Examples folder contains a folder for each chapter, e.g., 'Ch. 05 Interpolation Examples.' The examples folder for each chapter contains all of the examples discussed in that chapter spreadsheets, charts and VBA code. The location of the Excel file pertinent to each example is specified in the chapter text, usually in the caption of a figure, e.g., Figure 5-5. Using VLOOKUP and MATCH to...

## Goal Seek Custom Function

The Newton-Raphson custom function described in a previous section was modified to create a custom function that performs goal seeking. This custom function can be used in the same way as Excel's built-in Goal Seek tool to find the value of x the changing cell that makes the function y the target cell have a specified value. The VBA code is shown in Figure 8-40. Function GoalSeek target_cell, changing_cell, objective_value, Optional _ initial_value As Double 'Finds value of X to make Y have a...

## Passing an Indefinite Number of Arguments Using the Param Array Keyword

Occasionally a Function procedure needs to accept an indefinite number of arguments. The SUM worksheet function is an example of such a function its syntax is SUM numbeii,number2, . To enable a Function procedure to accept an indefinite number of arguments, use the ParamArray keyword in the argument list of the function, as in the following expression Function ArrayMaker ParamArray rng Only one argument can follow the ParamArray keyword, and it must be the last one in the function's list of...

## Calculating First and Second Derivatives

A pH titration measured volumes of a base solution are added to a solution of an acid and the pH measured after each addition is shown in Figure 6-1, and a portion of the spreadsheet containing the titration data in Figure 6-2. The endpoint of the titration corresponds to the point on the curve with maximum slope, and this point can be estimated visually in Figure 6-1. The first and second derivatives of the data are commonly used to determine the inflection point of the curve mathematically....

## Log vs Reciprocal The function

Is often seen in the relationship of physical properties to temperature. The linearized form is In y -b x a. This equation form is encountered in the Clausius-Clapeyron equation which relates vapor pressure of a pure substance to temperature, and the Arrhenius equation which relates rate constant k of a reaction to temperature. Trigonometric Functions. Excel's trigonometric functions require angles in radians. For an angle 6 in degrees, use 710 180. The function represented by equation A4-19 or...

## Cubic Interpolation

Often, values in a table change in such a way that linear interpolation is not suitable. Cubic interpolation uses the values of four adjacent table entries e.g., at x0, x , x2 and x3 to obtain the coefficients of the cubic equation y a bx cx2 dx to use as an interpolating function between Jti and jc2. For example, to find the freezing point for a 33.3 wt solution of ethylene glycol using cubic interpolation requires the four table values in Figure 5-12 whose x values are highlighted. A...

## Limitations of Trendline

The Trendline dialog box offers only a limited menu of mathematical fitting functions linear, polynomial, exponential, etc. And, in addition, the independent variables used in the regression must be mathematical functions of a single independent variable x, x2, x3, etc. LINEST, on the other hand, can perform multiple linear regression with several different independent variables. For example, in a study of the yield of a biomolecule produced by fermentation, regression analysis using LINEST, on...

## To Find the Point of Intersection of Two Lines

It is a simple matter to use Goal Seek to find the intersection of two lines, as illustrated in Figure 8-31 Figure 8-31. Finding the intersection of two lines in a chart, folder 'Chapter 08 Examples', workbook 'Intersecting Lines', sheet 'Two Straight Lines' Figure 8-31. Finding the intersection of two lines in a chart, folder 'Chapter 08 Examples', workbook 'Intersecting Lines', sheet 'Two Straight Lines' In the spreadsheet cells shown in Figure 8-32, the formula in cell B24 is slope1 A24 int1...

## Random Numbers and the Monte Carlo Method

The Monte Carlo method differs from the techniques we have considered in preceding chapters instead of applying quantitative mathematical expressions to arrive at an answer, we approximate or simulate the process, repeat the calculation a large number of times using randomly selected inputs chosen within a suitable range, and then average the result or draw other statistical conclusions. The method can be lengthy and provide only an approximate answer, but it may be the only available way to...

## Solving Parabolic Partial Differential Equations The Explicit Method

Using equation 12-21 as an example and writing it in the form we can replace derivatives by finite differences, using the central difference formula for lt FI3c2 and the forward difference formula for dFldy When these are substituted into equation 12-22, we obtain equation 12-25, where r Ay k Ax 2 . Using forward and central differences simplifies the expression. FiJ l r FMj Fi_lj l-r Fu 12-25 Or, when i represents distance x and j represents time t, r Fx u Fx_Xt l r Fxt 12-25a Equation 12-25a...

## The Gauss Jordan Method

The Gauss-Jordan method utilizes the same augmented matrix A C as was used in the Gaussian elimination method. In the Gaussian elimination method, only matrix elements below the pivot row were eliminated in the Gauss-Jordan method, elements both above and below the pivot row are eliminated, resulting in a unit coefficient matrix The advantage of this method is that the calculation of the vector of results is simplified. The VBA custom function GaussJordanl, shown in Figure 9-5 incorporates...

## InterpC

Performs cubic interpolation in a table of x- and -values, using the LaGrange 4th-order polynomial. Returns the interpolated -value corresponding to a specified x-value. InterpC lookup_value, known_x's, known_y's lookup_value the x-value for which you want to find the corresponding -value by cubic interpolation known_x's the range of x-values in the table independent variable known_y's the range of -values in the table dependent variable lookup_value can be either a number or a reference to a...

## Finite Difference Methods

As described in the following, approximating the derivative of a function by a finite difference quotient will allow us to reduce a boundary-value problem to a system of simultaneous equations that can be solved by methods that have been discussed in Chapter 9. Problems that are difficult or impossible to solve by the shooting method may sometimes be solved by the finite-difference method. Consider a two-point boundary value problem, where y is known at the ends of the range and the expression...

## An Example Deflection of a Simply Supported Beam

A simply supported beam a beam supported at the ends is bent downwards by the applied load, consisting of the weight of the beam itself plus any other loads. Figure 11-1. Diagram of a simply supported beam. The simply supported steel beam shown in Figure 11-1 supports a uniformly distributed load of 2000 lb ft. The length L of the span is 30 feet. The deflection downward bending displacement y of the beam as a function of distance x along the span of the beam is given by the second-order...

## Linear Interpolation in a Table by Using the Trend Worksheet Function

Excel provides the TREND worksheet function to perform linear interpolation in a table of data by means of a linear least-squares fit to all the data points in the table. But TREND can be used to perform linear interpolation between two adjacent data points. TREND known_y's, known_x's, new_x's, const where known_y's and known_x's are one-row or one-column ranges of known values. The argument new_x's is a range of cells containing values for which you want the interpolated value. Use the...

## Linear Interpolation in a Table by Means of Worksheet Formulas

To find the value of y at a point x that is intermediate between the table values xq, yo and Xj, use the equation for simple linear interpolation equation 5-1 . Figure 5-6. Freezing point of ethylene glycol solutions data from Figure 5-1 . folder 'Chapter 05 Interpolation', workbook 'Interpolation I', sheet 'Linear Interpolation' Figure 5-6. Freezing point of ethylene glycol solutions data from Figure 5-1 . folder 'Chapter 05 Interpolation', workbook 'Interpolation I', sheet 'Linear...

## Monte Carlo Simulation

The Monte Carlo method is any technique of random sampling employed to approximate solutions to quantitative problems. Often the system being simulated is clearly one that involves random processes, as, for example the Random Walk problem, sometimes described as the path a drunk takes as he staggers away from a telephone pole. If he takes N steps, each of length , and each in a completely random direction, how far will he be from the telephone pole after the N steps The problem can be solved...

## Systems of Simultaneous Equations

Sometimes a scientific or engineering problem can be represented by a set of n linear equations in n unknowns, for example ax aX2x 2 al3x3 alnx cx a2 x i a22x2 a23x3 a2,jc c2 anXx 1 a 2x2 a 3x3 annxn cn where x , X2, x , , xn are the experimental unknowns, c is the experimentally measured quantity, and the a,y are known coefficients. The equations must be linearly independent in other words, no equation is simply a multiple of another equation, or the sum of other equations. A familiar example...

## First and Second Derivatives of Data in a Table

The second derivative, cfy dx2, of a data set can be calculated in a similar manner, namely by calculating A Ay Ax Ax. Calculation of the first or second derivative of a data set tends to emphasize the noise in the data set that is, small errors in the measurements become relatively much more important. The central difference formula tends to reduce noise resulting from experimental error. Points on a curve of x, y values for which the first derivative is a maximum, a minimum, or zero are often...

## Examining the Values of Variables During Execution

You can also display the values of selected variables as the code is executed. There are several ways to select variables or expressions to be displayed Highlight the variable or expression and then choose Quick Watch from the Debug menu or press the Quick Watch button 1 on the Debug toolbar, to display the Quick Watch dialog box Figure 2-26 . Highlight the variable or expression and then choose Add Watch from the Debug menu to display the Add Watch dialog box Figure 227 . Figure 2-26. The VBA...