Vertex42 The Excel Nexus

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 Function Procedure

The first example is a Function procedure that returns the first derivative of a specific worksheet formula. The expression for the derivative is "hard-coded" in the VBA procedure. The user must be able to provide the expression for the derivative and must modify the VBA code to apply it to a different formula. The function's only argument is the value of x, the independent variable for which the derivative is to be calculated. The main advantage of this approach is that the returned value of the derivative is exact. This approach will execute the fastest and would be suitable if the same formula is to be used many times in a worksheet.

Function Derivl (x)

'User codes the expression for the derivative here. Derivl = 9*xA2+10*x-5 End Function

Figure 6-10. Function procedure to demonstrate calculation of a first derivative, (folder 'Chapter 06 Examples', workbook 'Derivs by VBA (Part 1)', module 'Module 1')

First Derivative of a Worksheet Formula Calculated by Using the Finite-Difference Method

The second example is a Function procedure that uses the finite-difference method. The first derivative of a formula in a worksheet cell can be obtained with a high degree of accuracy by evaluating the formula at x and at x + Ax. Since Excel carries 15 significant figures, Ax can be made very small. Under these conditions Ay/Ax approximates dy/dx very well.

The user must "hard-code" the worksheet formula in VBA, in a suitable form; the derivative is calculated by numerical differentiation. Again, the function's only argument is the value of x, the independent variable. This approach would be useful if the user is unable to provide an expression for the derivative.

Function Deriv2(x) OldY = fn(x) xx = (1.00000001) *x NewY = fn(xx)

Function fn(x)

'User codes the expression for the function here. fn = 3*xA3 + 5*xA2-5*x+11 End Function

Figure 6-11. Function procedure to demonstrate calculation of first derivative, (folder 'Chapter 06 Examples', workbook 'Derivs by VBA (Part 1)', module 'Module 1')

In the previous section, the finite-difference method was shown to provide an excellent estimate of the first derivative of a function expressed as a worksheet formula. The multiplier used in the preceding user-defined function was 1.00000001. What is the optimum value of this multiplier, so that the Newton quotient Ay!Ax gives the best approximation to dy/dx!

There are two sources of error in this finite-difference method of computing dyldx\ the approximation error, inherent in using a finite value of Ax, and the roundoff error, due to the limited precision of the numbers stored in the computer. We want to find the value of Ax that strikes the best balance between these two errors. If Ax is made too large, then the approximation error is large, since dy/dx Ay!Ax only when Ax -> 0. If Ax is made too small, then the roundoff error is large, since we are obtaining Ay by subtracting two large and nearly equal numbers, F(x) and F(x + Ax).

Excel carries 15 digits in its calculations, and it turns out that multiplying x by a factor of 1.00000001 (a change in the 8th place) produces the minimum error, before round-off error begins to have an effect. Figure 6-12 illustrates this, using a quadratic equation as an example; other functions give similar results. The values in Figure 6-12 show that we can expect accuracy up to approximately the tenth digit.

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

1 2 |
x y |
y = Sx2—11x + 30 Ax x+Ax y+Ay |
Ay/Ax exact |
% error | ||||

3 |
7.5 |
116.25 |
1 .0E-05 |
7.5001 |
116,253 |
31.0002 34,00002 |
34 34 |
6.6E-04 |

4 |
7.5 |
116.25 |
1 .QE-06 |
7.50001 |
116.2503 |
6.6E-05 | ||

5 |
7.5 116.25 1.0E-07 |
7.500001 |
116 25003 |
34 000002 |
34 |
6.7Ë-06 8.4E-07 | ||

6 |
7.5 116.25 1.0E-08 |
7.5000001 |
116.250003 |
34.0000003 |
34 | |||

7 |
7.5 116.25 1.0E-09 |
7.50000001 |
116.2500003 |
34.000001 |
34 |
4.2E-06 | ||

8 |
7.5 116.25 1.0E-10 |
7.500000001 |
116.25000003 34.00002 |
34 |
4.9E-05 | |||

9 |
7.5 116.25 |
1,0E-11 |
7.5000000001 |
116.250000003 34.0001 |
34 |
4.2E-04 |

Figure 6-12. Newton quotient Ay/Ax as a function of the magnitude of Ax (folder 'Chapter 06 Examples', workbook 'Derivs by VBA (Part 1)', sheet 'Newton Quotient')

Figure 6-12. Newton quotient Ay/Ax as a function of the magnitude of Ax (folder 'Chapter 06 Examples', workbook 'Derivs by VBA (Part 1)', sheet 'Newton Quotient')

Derivative of a Worksheet Formula Calculated by Using the Finite-Difference Method

The spreadsheet shown in Figure 6-13 (see folder 'Chapter 06 Examples', workbook 'Derivs by Sub Procedure') illustrates the calculation of the first derivative of a function y = x3-3x2- 13Ox + 150 by evaluating the function at x and at x + Ax. Here a value of Ax of 1 x 10~8 was used. For comparison, the first derivative was calculated from the exact expression from differential calculus: F(x) = 3x2 -6x— 130.

The Excel formulas in cells B11, C11, D11, E11, F11, G11 and H11 (columns C-F are hidden) are

E11 =A11*delta Ax

F11 =D11-B11 Ay

G11 =F11/E11 AyJAx

„„A |
B |
G |
H | |

1 |
Numerical Differentiation | |||

2 |
F 00 = |
txA3 + uxA2 + w + w | ||

3 |
t |
1 |
_I | |

4 |
u |
-3 |
delta | |

5 |
V |
-130 |
1.00E-08 | |

B |
w |
150 | ||

7 |
Ay/A)i | |||

8 |
X |
y |
By worksheet formula |
From calculus |

9 |
-10 |
150 |
230 0000006 |
230 |

10 |
-9 |
348 |
167.000005 |
167 |

11 |
-8 |
486 |
110.000002 |
110 |

12 |
-7 |
570 |
59.000001 |
59 |

13 |
-6 |
606 |
14,000002 |
14 |

14 |
-5 |
600 |
-24 9999994 |
-25 |

15 |
-4 |
553 |
-57,999998 |
-58 |

16 |
-3 |
486 |
-84 9999992 |
-85 |

17 |
-2 |
390 |
-105.999999 -106 | |

18 |
-1 |
276 |
-120 9999994 |
-121 |

19 |
0 |
150 |
#DM0! |
-130 |

Figure 6-13. First derivative calculated on a worksheet by using Ax. (folder 'Chapter 06 Examples', workbook 'Derivs by Sub Procedure', sheet 'Deriv')

Figure 6-13. First derivative calculated on a worksheet by using Ax. (folder 'Chapter 06 Examples', workbook 'Derivs by Sub Procedure', sheet 'Deriv')

The value in cell G21 illustrates that, using this technique, an x value of zero will have to be handled differently, since multiplying zero by 1.00000001 does not produce a change in x. This problem will be dealt with in a subsequent section.

First Derivative of a Worksheet Formula Calculated by Using a VBA Sub Procedure Using the Finite-Difference Method

The approach used in the preceding section can be performed by using a VBA Sub procedure. The VBA code is shown in Figure 6-14. By means of an input box the user identifies the range of cells containing the formulas for which the derivative is to be calculated, with a second input box, the corresponding cells containing the independent variable x, and with a third input box, the range of cells to receive the first derivative.

Option Explicit

Option Base 1

Sub Derivs()

Dim z As Integer, N As Integer

Dim Old_Ys() As Double, New_Ys() As Double, Old_Xs() As Double,

Dim Derivs() As Double, increment As Double

Dim known_Xs As Object, known_Ys As Object, eel As Object increment = 0.00000001

'Use the Set keyword to create an object variable Set known_Ys = Application.InputBox _

("Select the range of Y values", "STEP 1 OF 3" 8)

N = known_Ys.Count

ReDim Old_Ys(N), New_Ys(N), Old_Xs(N), Derivs(N) z = 1

For Each eel In known_Ys Old_Ys(z) = cel. Value z = z + 1 Next eel

Set known_Xs = Application.InputBox _

("Select the range of X values", "STEP 2 OF 3" 8)

For Each eel In known_Xs Old_Xs(z) = cel.Value cel.Value = Old_Xs(z) * (1 + increment) z = z + 1 Next eel z= 1

For Each eel In known_Ys New_Ys(z) = cel.Value z = z + 1 Next eel z= 1

For Each eel In known_Xs cel.Value = Old_Xs(z) z = z + 1 Next eel

Application.lnputBoxfSelect the destination for derivatives", _

Derivs(z) = (New_Ys(z) - Old_Ys(z)) / (increment * Old_Xs(z)) ActiveCell.Offset(z -1, 0).Value = Derivs(z) Next

End Sub

Figure 6-14. Sub procedure to calculate first derivative, (folder 'Chapter 06 Examples', workbook 'Derivs by Sub Procedure', module 'Derivs')

A |
B I |
G_______________ |
_ . « i |
I | |

1 |
Numerical Differentiation | ||||

7 |
F(x) = txA3 + ux*2 + vx + w | ||||

3 |
Î |
1 | |||

4 |
u |
-3 |
delta | ||

5 |
V |
-130 |
1.00E-08 | ||

6 |
w |
150 | |||

7 |
Ay/Ax | ||||

8 |
X |
y |
By worksheet formula |
From calculus |
By macro |

9 |
-10 |
150 |
230.0000006 |
230 |
230.000001 |

1D |
-9 |
348 |
167.000005 |
167 |
167.000005 |

11 |
-8 |
486 |
110.000002 |
110 |
110,000002 |

12 |
-7 |
570 |
59.000001 |
59 |
59.0000013 |

13 |
-6 |
606 |
14.000002 |
14 |
14.0000016 |

14 |
-5 |
600 |
-24.9999994 |
-25 |
-24.9999994 |

15 |
-4 |
558 |
-57.999998 |
-58 |
-57.999998 |

16 |
-3 |
486 |
-84.9999992 |
-85 |
-84.9999992 |

17 |
-2 |
390 |
-105.999999 |
-106 |
-105.999999 |

18 |
-1 |
276 |
-120.9999994 |
• •• ; -r~'- | |

19 |
0 |
150 |
#DIV/0l |
-130 | |

20 |
1 |
1 18 " |
-132.999998 |
-133 |
-133 |

Figure 6-15. Calculating the first derivative of a formula, (folder 'Chapter 06 Examples', workbook 'Derivs by Sub Procedure', sheet 'Deriv')

Figure 6-15. Calculating the first derivative of a formula, (folder 'Chapter 06 Examples', workbook 'Derivs by Sub Procedure', sheet 'Deriv')

The Sub procedure saves the values of x and y from the worksheet (OldX and OldY), then writes the incremented value of x (NewX) to the worksheet cell. This causes the worksheet to recalculate and display the corresponding value of y + Ay (NewY). The derivative is calculated and written to the destination cell. Finally, the original value of x is restored. Figure 6-15 illustrates the spreadsheet of Figure 6-13 after the Sub procedure has been run. The errors produced by this method are much smaller than those produced by the function based on LI NEST.

The code in Figure 6-14 can easily be modified to calculate the partial derivatives of a function with respect to one or several parameters of the function (e.g., dy/da, dy/db, etc.) for a cubic equation. Similar code is used in the SolvStat macro (see Chapter 14, "The Solver Statistics Add-In") and a similar approach is used in the Solver itself (see "How the Solver Works" in Chapter 14).

-800

-200

-800

-200

Figure 6-16. A chart of a function and its first derivative, (folder 'Chapter 06 Examples', workbook 'Derivs by Sub Procedure', sheet 'Deriv')

The advantage of using a Sub procedure is that the derivative can be obtained easily, even for the most complicated worksheet formulas. All of the difficult calculations are done when the spreadsheet updates after the new value of x is entered in, for example, cell A9. The disadvantage of a Sub procedure is that if changes are made to precedent cells in the worksheet, the Sub procedure must be run in order to update the calculations.

First Derivative of a Worksheet Formula Calculated by Using a VBA Function Procedure Using the Finite-Difference Method

Unlike the Sub procedure described in the preceding section, a Function procedure automatically recalculates each time changes are made to precedent cells. A Function procedure to calculate the first derivative of a formula in a cell would be very useful. However, a function procedure can't use the approach of the preceding section (i.e., changing the value of the cell containing the x value), since a function procedure can't change the contents of other cells. A different approach will have to be found.

The following VBA code illustrates a simple Function procedure to calculate the first derivative dy/dx of a formula in cell, using the same approach that was used in the preceding section: the procedure calculates OldX, OldY,

NewX and NewY in order to calculate Ax/Ay. But in this function procedure, both the worksheet formula and the independent variable are passed to the function as arguments. The procedure is shown simply to illustrate the method; a number of modifications, to be described later, will be necessary in order to produce a "bulletproof' procedure.

The basic principle used in this Function procedure is the following:

(i) The two arguments of the function are references to the independent variable x and the cell containing the formula to be differentiated, F(x).

(ii) Use the Value property to obtain the values of the arguments; these are OldX and OldY.

(iii) Use the Formula property of the cell to get the worksheet formula to be differentiated as the text variable FormulaText.

(iv) Use the SUBSTITUTE worksheet function to replace references to the x variable in FormulaText by the incremented x value, NewX.

(v) Use the Evaluate method to get the new value of the formula. This is NewY.

Since other procedures in this chapter and in subsequent chapters will use the same method for modifying and evaluating a formula, it will be worthwhile to examine the VBA code shown in Figure 6-17. The syntax of the function is FirstDerivDemo(express/on, variable). The nine lines of code in this procedure perform the following actions:

(1) Get FormulaString, the worksheet formula (as text) by using the Formula property of expression.

(2) Get OldY, the value of the worksheet formula, by using the Value property of expression.

(3) Get XRef, the reference to the independent variable x, by using the Address property of variable. The address will be an A1-style absolute reference

(4) Get OldX, the value of the independent variable x, by using the Value property of variable.

(5) Calculate NewX, the incremented value of the independent variable, by multiplying OldX by 1.000000001.

(6) Convert all references in FormulaString to absolute by using the ConvertFormula method.

(7) Replace all instances of XRef in FormulaString by the value of the new variable NewX. This is done by using the SUBSTITUTE worksheet function. For example, the formula string

=3*$B$3A3+5*$B$3A2-5*$B$3+11 when cell $B$3 contains the value 2, is converted to =3*2.00000002A3+5*2.00000002A2-5*x+11.

(8) Calculate NewY, the new value of the function, by applying the Evaluate method to the new formula string.

(9) Calculate and return the first derivative.

Option Explicit

Function FirstDerivDemo(expression, variable) As Double 'Custom function to return the first derivative of a formula in a cell.

Dim OldX As Double, OldY As Double, NewX As Double, NewY As Double Dim FormulaString As String, XAddress As String

FormulaString = expression.Formula OldY = expression.Value

XAddress = variable.Address 'Default is absolute reference OldX = variable. Value NewX = OldX * 1.00000001

FormulaString = Application.ConvertFormula(FormulaString, xlA1, xlA1, _ xIAbsolute) 'Convert all references in formula to absolute

FormulaString = Application.Substitute(FormulaString, XAddress, NewX) NewY = Evaluate(FormulaString)

End Function_

Figure 6-17. Function procedure to demonstrate calculation of first derivative, (folder 'Chapter 06 Examples', workbook 'Derivs by VBA (Part 2)', module 'Demo')

Examples of the first derivative of some worksheet formulas calculated by the custom function are shown in Figure 6-18. The formula in cell D3 is = FirstDerivDemo (C3,B3)

The formulas labeled "exact" in column E are the appropriate formulas from differential calculus for the first derivative of the respective functions. For example, the formula in cell E3 is =9*B3A2+10*B3-5

A |
B |
0 |
D |
E |
F | |

1 |
Demo to illustrate Use of Simple First Derivative Funet!on | |||||

2 |
Function |
X |
FÇK) |
F'00 |
exact |
% error |

3 |
y=3xJ+5r-5x+11 |
2 |
45 |
51.0000003 |
51 -5.2E-07 | |

4 |
y = sin x |
1 |
0.84147 |
0.5403023 |
0.5403023 |
-5.8E-08 |

5 |
y=e"* |
-1 |
0.36788 |
0.3678794 |
0.3678794 |
2.5E-07 |

6 |
y= a*(e.g., a = 3.5) |
2.4 |
19.4734 |
24.3955256 |
24.3955252 |
-1.5E-06 |

Figure 6-18. Using a simple Function procedure to calculate some first derivatives, (folder 'Chapter 06 Examples', workbook 'Derivs by VBA (Part 2)', sheet 'Demo Function')

Figure 6-18. Using a simple Function procedure to calculate some first derivatives, (folder 'Chapter 06 Examples', workbook 'Derivs by VBA (Part 2)', sheet 'Demo Function')

Improving the VBA Function Procedure

The simple procedure shown in Figure 6-17 requires some modification.

First, the simple procedure replaces all instances of XRef, the reference to the independent variable x, in FormulaString with a number value. For example, a cell reference such as A2 will be replaced with a number value such as 0.05. But there are cases where the substring A2 should not be replaced. Our procedure needs to handle the following possibilities, all of which contain the substring A2 within FormulaString:

(i) the reference XRef and references in FormulaString may be relative, absolute or mixed,

(ii) FormulaString contains a name such as BETA2,

(iii) FormulaString contains a reference such as AA2, or

(iv) FormulaString contains a reference such as A25.

By using the Address property to obtain an absolute reference (e.g., $A$2) and using the ConvertFormula method to convert all references in FormulaString to absolute, we have already eliminated problems arising from cases (i), (ii), and (iii). Only case (iv) poses a problem: the substring $A$2 in $A$25 will be substituted by 0.05, yielding 0.055. And so, as is often the case with computer programming, a project that initially appeared to be simple requires some additional programming.

We could write a formula parser that would break FormulaString into its component parts and inspect each one. Not impossible, but that would require extensive programming. A much simpler solution turns out to be the following: by means of a loop, we replace each instance of, for example, A2 individually, and, instead of replacing the reference with a number (e.g., 0.05), we replace the reference with the number concatenated with the space character (e.g., 0.05 0). We then evaluate the resulting string after each substitution. The reference $A$25 yields the string 0.05 5. When evaluated, this gives rise to an error, and an On Error GoTo statement is used so that the faulty substitution is not incorporated into the FormulaString to be evaluated. Inspection of the code in the latter half of the procedure in Figure 6-21 should make the process clear.

A second problem with the simple procedure of Figure 6-17 is that when x = 0, NewX = OldX, NewY = OldY and the procedure returns a #VALUE! error. The error produced by a zero value for the independent variable x is handled by adding an additional optional argument scale_factor. The syntax of the function is dydx(expression, reference, Optional scale_factor). If x is zero, a value for scale_factor must be entered by the user. Scale_factor is used to calculate the Ax for numerical differentiation. Scale_factor should be the same order of magnitude as typical x values used in the formula.

The Function procedure is shown in Figure 6-19.

Option Explicit

Function dydx(expression, variable, Optional scale_factor) As Double

'Custom function to return the first derivative of a formula in a cell.

'expression is F(x), variable is x.

'scale_factor is used to handle case where x = 0.

'Workbook can be set to either R1C1- or A1-style.

Dim OldX As Double, NewX As Double, OldY As Double, NewY As Double

Dim delta As Double

Dim NRepI As Integer, J As Integer

Dim FormulaString As String, XRef As String, dummy as String Dim T As String, temp As String delta = 0.00000001

'Get formula and value of cell formula (y).

FormulaString = expression.Formula 'Returns A1 -style formula; default is absolute.

OldY = expression.Value

'Get reference and value of argument (x).

OldX = variable.Value

XRef = variable.Address 'Default is A1-style absolute reference.

'Handle the case where x = 0. 'Use optional scale_factor to provide magnitude of x. 'If not provided, returns #DIV0! If OldX <> 0 Then NewX = OldX • (1 + delta) Else

If lsMissing(scale_factor) Or scale_factor = 0 Then _ dydx = CVErr(xlErrDivO): Exit Function NewX = scale_factor * delta End If

'Convert all references to absolute

'so that only text that is a reference will be replaced.

T = Application.ConvertFormula(FormulaString, xlA1, xlA1, xlAbsolute)

'Do substitution of all instances of x reference with value. 'Substitute reference, e.g., $A$2, 'with a number value, e.g., 0.2, followed by a space 'so that $A$25 becomes 0.2 5, which results in an error. 'Must replace from last to first.

NRepI = (Len(T) - Len(Application.Substitute(T, XRef,""))) / Len(XRef) For J = NRepI To 1 Step -1 temp = Application.Substituted, XRef, NewX &"", J) If lsError(Evaluate(temp)) Then GoTo pt1 T = temp pt1: Next J NewY = Evaluate(T) dydx = (NewY - OldY) / (NewX - OldX) End Function

Figure 6-19. Improved Function procedure to calculate first derivative, (folder 'Chapter 06 Examples', workbook 'Derivs by VBA (Part 2)', module 'FirstDeriv')

A |
B |
C |
D |
E |
F | |

1 |
Demo to Illustrate Use of Advanced First Derivative Function | |||||

2 |
Reference in formula or in argument can be absolute, relative, mixed or a name. | |||||

3 |
Function |
X |
F(x) |
m |
exact |
% error |

4 |
y=3xJ+5x:-5x+11 |
45 |
51.00000027 |
51 |
-5.2E-07 | |

5 |
y= sin x |
1 |
0.84147 |
0.54030231 |
0.5403023 |
-5.8E-08 |

6 |
-1 |
0.36788 |
0.36787944 |
0.3678794 |
2.5E-07 | |

7 |
y= a'(e.g., a= 3.5) |
2.4 |
19.4734 |
24.39552511 |
24.395525 |
3.8E-07 |

8 |
y=3x3+5)r-5x+11 |
0 |
11 |
#VALUE! |
-5 |
LVALUE! |

9 |
y=3x3+5)T-5x+11 |
0 |
11 |
-4,99999988 |
-5 |
2.4E-06 |

Figure 6-20. Using the improved function procedure to calculate some first derivatives. The optional argument scale_factor is used in row 9 to eliminate the #VALUE! error seen in row 8. (folder 'Chapter 06 Examples', workbook 'Derivs by VBA (Part 2)', sheet Better Function')

Figure 6-20. Using the improved function procedure to calculate some first derivatives. The optional argument scale_factor is used in row 9 to eliminate the #VALUE! error seen in row 8. (folder 'Chapter 06 Examples', workbook 'Derivs by VBA (Part 2)', sheet Better Function')

The examples in Table 6-20 illustrate the values of the first derivative calculated by using the function dydx, compared with the "exact" values.

The worksheet formulas in column C and the corresponding functions in column D are:

Rows 4-6 illustrate that relative, absolute or mixed references can be used in the worksheet formula or in the arguments of the custom function. Row 9 illustrates the use of the optional argument scale_factor when the x value is zero.

The VBA code for the Function procedure shown in Figure 6-21 requires only slight modification to provide a function that returns the second derivative of a function as a cell formula. The syntax of the d2xdy2 function is identical to that of the function dydx.

The code is shown in Figure 6-21. The function calculates the central derivative uing three points (see the formula in Table 6-1). Note that the multiplier used to calculate Ax is 1E-4 instead of 1E-8.

Option Explicit

Function d2ydx2(expression, variable, Optional scale_factor) As Double

'Custom function to return the second derivative of a formula in a cell.

'expression is F(x), variable is x.

'Uses central difference formula.

'scale_factor is used to handle case where x = 0.

'Workbook can be set to either R1C1- or A1-style.

Dim OldX As Double, OldY As Double Dim NewX1 As Double, NewX2 As Double Dim NewY1 As Double, NewY2 As Double Dim XRef As String Dim delta As Double

Dim FormulaString As String, T As String

Dim temp As String

Dim NRepI As Integer, J As Integer delta = 0.0001

'Get formula and value of cell formula (y).

FormulaString = expression.Formula 'Returns A1-style formula

OldY = expression.Value

'Get reference and value of argument (x).

OldX = variable.Value

XRef = variable.Address 'Default is A1 -style absolute reference

'Handle the case where x = 0. 'Use optional scale_factor to provide magnitude of x. 'If not provided, returns #DIV0! If OldX <> 0 Then NewX1 = OldX * (1 + delta) NewX2 = OldX * (1 - delta) Else

If lsMissing(scale_factor) Or scale_factor = 0 Then _ d2ydx2 = CVErr(xlErrDivO): Exit Function NewX1 = scale_factor" delta NewX2 = -scale_factor * delta End If

'Convert all references to absolute

'so that only text that is a reference will be replaced.

FormulaString = AppHcation.ConvertFormula(FormulaString, xlA1, xlA1, xIAbsolute)

T = FormulaString

NRepI = (Len(T) - Len(Application.Substitute(T, XRef,""))) / Len(XRef) 'Do substitution of all instances of x reference with incremented x value For J = NRepI To 1 Step-1 temp = Application.Substitute(T, XRef, NewX1 &"", J) If lsError(Evaluate(temp)) Then GoTo pt1 T = temp pt1: Next J

'Evaluate the expression.

T = FormulaString

'Now do substitution of all instances of x reference with decremented x value For J = NRepI To 1 Step -1 temp = Application.Substitute(T, XRef, NewX2 &"", J) If lsError(Evaluate(temp)) Then GoTo pt2 T = temp pt2: Next J

NewY2 = Evaluate(T)

d2ydx2 = (NewY1 + NewY2 - 2 * OldY) / Abs((NewX1 - OldX) * (NewX2 - OldX)) End Function

Figure 6-21. Function procedure to calculate second derivative, (folder 'Chapter 06 Examples', workbook 'Derivs by VBA (Part 2)', module 'SecondDeriv')

Figure 6-22 illustrates the use of the dydx and d2ydx2 custom functions. The formula in cell B4 is

=aa*A4A3+bb*A4A2+cc*A4+dd

(aa, bb, cc, dd are named ranges. The formula in cell C4 is =dydx(B4,A4,1)

A |
B |
c |
D !. E |
F |
G |
H i | ||

1 |
First and Second Derivative Functions | |||||||

2 |
y = |
= 2x3 - |
- 20x3 + 11 x + 30 | |||||

3 |
X |
m |
m |
exact |
% error |
Fa(x) |
exact |
% error |

4 |
-5 |
-775 |
361 0000021 |
361 |
5.8E-07 |
-100.0000002 |
-100 |
-2.0E-07 |

5 |
-4 |
-462 |
267.0000003 |
267 |
1.0E-07 |
-88,0000002 |
-88 |
-2.0E-07 |

6 |
•3 |
-237 |
1850000020 |
185 |
1.1E-06 |
-75,9999997 |
-76 |
-4.5E-07 |

7 |
-2 |
-88 |
114.9999996 |
115 |
3.9E-07 |
-64.0000003 |
-64 |
-5.0E-07 |

8 |
-1 |
-3 |
57.0000001 |
57 |
1.6E-07 |
-52.0000000 |
-52 |
-7.5E-08 |

9 |
0 |
30 |
10 9999998 |
11 |
1.4E-06 |
-40.0000001 |
-40 |
-2.8E-07 |

10 |
1 |
23 |
-22.9999999 |
-23 |
-3.9E-07 |
-28.0000002 |
-28 |
-6.6E-07 |

11 |
2 |
-12 |
-45.0000001 |
-45 |
-2.0E-07 |
-15.9999999 |
-16 |
-S.1E-07 |

12 |
3 |
-63 |
-55.0000004 |
-55 |
-7.0E-07 |
-4.0000003 |
-4 |
-8.3E-06 |

13 |
4 |
-118 |
-52.9999997 |
-53 |
-5.0E-07 |
7.9999998 |
8 |
2.8E-06 |

14 |
5 |
-165 |
-38.9999993 |
-39 |
-1.7E-06 |
19.9999999 |
20 |
2.5E-07 |

Figure 6-22. Using Function procedures to calculate first and second derivatives of a function, (folder 'Chapter 06 Examples', workbook 'Derivs by VBA (Part 2)', sheet 'First and Second Derivs')

Figure 6-22. Using Function procedures to calculate first and second derivatives of a function, (folder 'Chapter 06 Examples', workbook 'Derivs by VBA (Part 2)', sheet 'First and Second Derivs')

Note the use of the optional argument scale_factor that prevents an error in cells C9 and F9 when the value of the independent variable in cell A9 is zero.

Concerning the Choice of Ax for the Finite-Difference Method

In preceding sections, the x + Ax used for the calculation of the derivatives was calculated by multiplying x by 1.00000001. Thus Ax is a "scaled" increment. An alternative approach would have been to use a constant Ax of, e.g., 0.00000001. Either approach has its advantages and disadvantages.

The constant-increment method eliminates the need to handle the case of x = 0 separately. However, the method fails when x is very large, e.g., 108. The scaled-increment method handles a wide range of x values, but fails in some special cases, such as for sin x when x = 1000.

You should be aware of these limitations when using the dydx and d2ydx2 custom functions.

Answers to the following problems are found in the folder "Ch. 06 (Differentiation)" in the "Problems & Solutions" folder on the CD.

1. Using the data file "Titration Curve", obtain the first and second derivative. The "endpoint" of a titration is considered to be the volume at the "inflexion point": that is, where the curve y = F(x) has maximum slope, or where the first derivative reaches a maximum, or where the second derivative passes through zero; the last is the easiest to determine graphically or mathematically.

2. Using the data file "Student Potentiometric Data", obtain the first and second derivative.

3. Using Excel's SIN function, create a table of sin<9, in one degree increments of 9 (remember that Excel's trigonometric functions require angles in radians). Now calculate d sin#, using one of the formulas in Table 6-1. Compare your answer with the exact: d sin# = cosft Experiment with different formulas from Table 6-1 to compare the errors.

4. Determine the first and second derivatives of the function y = 2x3 - 20x2 +1 lx + 30 over the range x = -5 to x - 10.

5. Determine the first derivative of the function y-x2 - 1 x 10~6;t +1 x 10~15 over the range x = 0 to x = 2 x 10~6.

6. Determine the first derivative of the following functions over suitable ranges of x:

G^iin

7. Show that the slope of the logistic equation

1 + e-ax at its midpoint (the Hall slope) is equal to a/4.

8. The van der Waals equation is an equation of state that applies to real gases. For 1 mole of a gas, the van der Waals equation is

where R is the gas constant (0.0821 L atm KT1 moP1) and T is the Kelvin temperature. The constants a and b are constants particular to a given gas, and correct for the attractive forces between gas molecules, and for the volume occupied by the gas molecules, respectively. For methane (CH4), the constants are a = 2.253 L2 atm and b = 4.278 x 10"2 L. Using the rearranged form of the van der Waals equation r ^ a V-b V2

calculate the pressure of 1 mole of methane as a function of container volume at 0°C (273 K) at suitable volumes from 22.4 L to 0.05 L. Use one of the custom functions described in this chapter to calculate the first and second derivatives of the P-V relationship. Compare with the exact expressions dP _ RT | 2a dV (v-b)2 V

This Page Intentionally Left Blank

Was this article helpful?

## Post a comment