how to get a derivative of a graph in excel
how to get a derivative of a graph in excel
(OP)
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
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





RE: how to get a derivative of a graph in excel
Cheers,
Joerd
Please see FAQ731-376: Eng-Tips.com Forum Policies for tips on how to make the best use of Eng-Tips.
RE: how to get a derivative of a graph in excel
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 (y) 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(n) = (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
RE: how to get a derivative of a graph in excel
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.
RE: how to get a derivative of a graph in excel
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: Eng-Tips.com Forum Policies for tips on how to make the best use of Eng-Tips.
RE: how to get a derivative of a graph in excel
Cheers,
Joerd
Please see FAQ731-376: Eng-Tips.com Forum Policies for tips on how to make the best use of Eng-Tips.
RE: how to get a derivative of a graph in excel
RE: how to get a derivative of a graph in excel
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
RE: how to get a derivative of a graph in excel
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...