how to get a derivative of a graph in excel 3

[vinny31]

Hi
i have a graph of a displacement curve as follows
i wish to get the derivative of it using excel.
can anyone advise


TIME UY_2
0 -2.58E-05
1.00E-02 -2.58E-05
2.00E-02 -2.84E-04
3.00E-02 -1.19E-03
4.00E-02 -3.17E-03
5.00E-02 -6.57E-03
7.50E-02 -2.60E-02
0.1 -6.53E-02
0.125 -0.12476
0.15 -0.19994
0.175 -0.28507
0.2 -0.37615
0.225 -0.47174
0.25 -0.57128
0.275 -0.67416
0.3 -0.77949
0.325 -0.88627
0.35 -0.99361
0.375 -1.1007
0.4 -1.207
0.425 -1.3142

 

[joerd]

Just compute [Δ]TIME/[Δ]UY_2, e.g. in cell C2: [tt]=(B2-B1)/(A2-A1)[/tt]

Cheers,
Joerd

Please see FAQ731-376 for tips on how to make the best use of Eng-Tips.

 

[prost]

How about something more sophisticated? Here's a cubic spline routine I've adapted to give you first (dy/dx) and
second derivatives d/dx (dy/dx)==dydx2
Data in Excel worksheet looks like this. Warning! Don't trust the derivatives near the end points (that is, first and last points in the Col A). To make a long story short, to do the cubic spline you have to more or less arbitrarily assign boundary conditions at the end points, there are at least 3 ways I know to 'guess' those boundary conditions, and these derivatives are very sensitive to those boundary conditions. (I tried this with y=x*x points, dy/dx and d2y/dx2 looked great away from the end points).

Col A Col B col D Col E col F Col G
row 1 ((blanks))
row 2: Actual Data Spline Interpol. dydx dydx2
row 3: x y x y
row 4: 0.00000 1.05361 0.00000
row 5: 0.0001 1.05355 0.01
row 6: 0.0107 1.04761 0.02
row ...

(in words...the data you know is in columns A and B, column C is blank, Column D contains the x values to interpolate to find the y values in Column E, columns F and G yield the dy/dx and the d2y/dx2)

Option Explicit
Option Base 1
Sub spline()
Dim xin() As Double, yin() As Double, cspline As Double, xinterp As Double
Dim yt() As Double, u() As Double, y As Double, dydx As Double, dydx2 As Double
Dim p, qn, sig, un, h, b, a As Double
Dim c As Long, Ctr As Long, FirstRow As Long, FinalRow As Long, irow As Long
Dim icol As Long
Dim n As Long, i As Long, k As Long 'these are loop counting integers
Dim klo, khi As Long
' First row for data is Row 4.
FirstRow = 4
' Find last row of data, helps you figure out how many data points to read.
FinalRow = Range("A65536").End(xlUp).Row
Ctr = FinalRow - FirstRow + 1
MsgBox ("No. pts: ") & Ctr
ReDim xin(Ctr)
ReDim yin(Ctr)
ReDim yt(Ctr)
' Columns A and B have the data you are fitting
For c = 1 To Ctr
xin(c) = Cells(c + FirstRow - 1, 1).Value
yin(c) = Cells(c + FirstRow - 1, 2).Value
Next c
' Find cubic spline coefficients, yt
cspline = cubic_spline(xin, yin, yt)
' Loop through all xinterp values
irow = FirstRow
icol = 4 'column for dumping interpolated data into
Do While ActiveSheet.Cells(irow, icol) <> ""
xinterp = Cells(irow, icol).Value
''''''''''''''''''''
'now eval spline at one point
'''''''''''''''''''''
' first find correct interval
klo = 1
khi = Ctr
Do
k = khi - klo
If xin(k) > xinterp Then
khi = k
Else
klo = k
End If
k = khi - klo
Loop While k > 1
h = xin(khi) - xin(klo)
a = (xin(khi) - xinterp) / h
b = (xinterp - xin(klo)) / h
y = a * yin(klo) + b * yin(khi) + ((a ^ 3 - a) * yt(klo) + _
(b ^ 3 - b) * yt(khi)) * (h ^ 2) / 6
dydx = (yin(khi) - yin(klo)) / h - ((3 * a ^ 3 - 1) / 6#) * h * yt(klo) + _
((3 * b ^ 3 - 1) / 6#) * h * yt(khi)
dydx2 = a * yt(klo) + b * yt(khi)
Cells(irow, icol + 1).Value = y
Cells(irow, icol + 2).Value = dydx
Cells(irow, icol + 3).Value = dydx2
irow = irow + 1
Loop
End Sub
Function cubic_spline(xin As Variant, yin As Variant, yt As Variant) As Double
' Given a data set consisting of a list of x and y values, this function
' smoothly interpolates resulting output value from a given input (x)
' value using a cubic spline interpolation. This counts how many points are
' in "input" and "output" set of data
Dim u() As Double
Dim p, qn, sig, un As Double
Dim n As Long, i As Long, k As Long 'loop counting integers
Dim klo, khi As Long
'''''''''''''''''''''''''''''''''''''''
' population of values
'''''''''''''''''''''''''''''''''''''''
n = UBound(xin)
ReDim u(n - 1)

yt(1) = 0
u(1) = 0

For i = 2 To n - 1
sig = (xin(i) - xin(i - 1)) / (xin(i + 1) - xin(i - 1))
p = sig * yt(i - 1) + 2
yt(i) = (sig - 1) / p
u(i) = (yin(i + 1) - yin(i)) / (xin(i + 1) - xin(i)) - (yin(i) - yin(i - 1)) / (xin(i) - xin(i - 1))
u(i) = (6 * u(i) / (xin(i + 1) - xin(i - 1)) - sig * u(i - 1)) / p
Next i

qn = 0
un = 0

yt = (un - qn * u(n - 1)) / (qn * yt(n - 1) + 1)

For k = n - 1 To 1 Step -1
yt(k) = yt(k) * yt(k + 1) + u(k)
Next k

End Function

 

[cowski]

You may also find this link useful:

http://www.nrbook.com/a/bookfpdf.php

numerical derivatives are discussed in section 5.7. You will probably find it less useful than the excellent suggestions already given, but the entire site is a good resource when you need to make your own subroutines.

 

[GregLocock]

Joerd, your method calculates a lagging derivative. It is often worth recomputing the average time over which you have taken the derivative.

Another point is that derivatives are very noisy, you might want to consider using a low pass filter (moving average for example) on the output.



Cheers

Greg Locock

Please see FAQ731-376 for tips on how to make the best use of Eng-Tips.

 

[joerd]

You're right, Greg, thanks.

Cheers,
Joerd

Please see FAQ731-376 for tips on how to make the best use of Eng-Tips.

 

[prost]

The noisiness of derivatives compared to the smoothness of the integrals is the primary reason why, IMO, implementations of finite element methods (which use integrals) are almost always stable while implementations of finite difference methods (which use linear or quadratic approximations to compute derivatives) are unstable for many initial and boundary conditions. Same thing goes when analyzing experimental data and applying stability and control algorithms--integrate whenever possible, differentiate only when absolutely necessary.

 

[Higgler]

Here's a semi-automated method.
You can add a trend line (of various different weightings) to an Excel plot, have it show the formula for that trend line, take the formula and calculate your own derivative, then you'll have a formula for derivitive that you can plot which will be instantaneously accurate.

kch

 

[MGMWoodworks]

Using the Data - Graph it.... (X-Y Scatter)

Use a Trend Line - 3rd order Polynomial worked for me... R^2 value of .9999 (Curve Fit Accuracy) for this dataset...

Use the Option to put the Equation of the line on the chart...

Calculate the derivative of that line... I get...

Y` = 51.576 * X^2 - 32.52 * X^1 + 0.7416

For the derivative and:

Y = 17.192 * X^3 - 16.26 * X^2 + 0,7416 * X - 0.0043 for the equation of the line itself, plotted data...