## Some Equations for Curve Fitting

This appendix describes a number of equation types that can be used for curve fitting. Some of the equation types can be handled by Excel's Trendline utility for charts these cases are noted below. Multiple Regression. Multiple regression fits data to a model that defines y as a function of two or more independent x variables. For example, you might want to fit the yield of a biological fermentation product as a function of temperature 7 , pressure of C02 gas P in the fermenter and fermentation...

## The Fourth Order Runge Kutta Method

The Runge-Kutta methods for numerical solution of the differential equation dy dx F x, y involve, in effect, the evaluation of the differential function at intermediate points between x and xn . The value of y is obtained by appropriate summation of the intermediate terms in a single equation. The most widely used Runge-Kutta formula involves terms evaluated at x , xn Ax 2 and x ax- The fourth-order Runge-Kutta equations for dy dx F x, y are where Tx F x ,y 10-8 T2 F x gt y 10-9 r4 F xn Ax,y T3...

## Adding Noise to a Signal Generated by a Formula

One of the simplest uses for the RAND function is to add noise to a theoretical curve generated by means of a formula, so as to simulate a real signal. In other words, we want to modify our worksheet formula F x by adding a random quantity 8. The 8 must be scaled to produce a noise term of suitable magnitude and the 8 terms must be equally distributed between positive and negative. Remember that RAND always returns a number greater than or equal to 0 and less than 1. There are several ways that...

## 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...

## To Find All Roots of a Regular Polynomial

A regular polynomial is one that contains only integer powers of x. The Bairstow or Bairstow-Lin method finds all roots, both real and imaginary, of a regular polynomial with real coefficients. The method involves the successive extraction of quadratic factors from the original polynomial of degree n and subsequent reduced polynomials of degree n-2, AM and so on. The quadratic formula is then used to obtain pairs of roots, either real or complex, from the quadratic factors. If the degree of the...

## 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...

## 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...

## 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...

## 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...

## Arrays Matrices and Determinants

Spreadsheet calculations lend themselves almost automatically to the use of arrays of values. Arrays in Excel can be either one- or two-dimensional. For the solution of many types of problem, it is convenient to manipulate an entire rectangular array of values as a unit. Such an array is termed a matrix. In Excel, the terms range, array and matrix are virtually interchangeable. Anmxn matrix m rows and n columns of values is illustrated below The values comprising the array are called matrix...

## 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...

## Crank Nicholson Custom Function

Using a smaller increment for At improves the accuracy of the calculations. It may be desirable to employ a variable value for At, so as to use smaller At near the beginning and use larger At where the function is not changing rapidly. This obviously can't be done with the spreadsheets in the preceding examples, since At determines the value of r and thus the values in the coefficients matrix. The following VBA code implements the Crank-Nicholson method. The partial differential equation must...

## Nonlinear Least Squares Curve Fitting

Unlike for linear regression, there are no analytical expressions to obtain the set of regression coefficients for a fitting function that is nonlinear in its coefficients. To perform nonlinear regression, we must essentially use trial-and-error to find the set of coefficients that minimize the sum of squares of differences between _ycaic and _yobsd. For data such as in Figure 14-1, we could proceed in the following manner using reasonable guesses for k and k2, calculate B at each time data...

## 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...

## 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...

## 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...

## 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...

## Chapter Differentiation

The analysis of scientific or engineering data often requires the calculation of the first or higher derivative of a function or of a curve defined by a table of data points. These derivative values may be needed to solve problems involving the slope of a curve, the velocity or acceleration of an object, or for other calculations. Students in calculus courses learn mathematical expressions for the derivatives of many types of functions. But there are many other functions for which it is...

## 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...

## 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...

## 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....

## 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...

## Statistics of Nonlinear Regression

The only problem with the use of the Solver to perform least-squares regression is that, although you get the regression coefficients readily, the results aren't much use if you don't know their uncertainties as well. These aren't available from the Solver. The following illustrates how to obtain the standard deviations of the regression coefficients after obtaining the coefficients by using the Solver. The standard deviation of the regression parameter a is given by equation 14-5. where P -'...

## 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...

## 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...

## 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...

## 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...