how to plot a graph in excel and find its derivative

' value using a cubic spline interpolation.Let us now understand the meaning of each of the terms in the output. ' smoothly interpolates resulting output (y) value from a given input (x) ' Given a data set consisting of a list of x and y values, this function Y = a * yin(klo) + b * yin(khi) + ((a ^ 3 - a) * yt(klo) + _ĭydx = (yin(khi) - yin(klo)) / h - ((3 * a ^ 3 - 1) / 6#) * h * yt(klo) + _įunction cubic_spline(xin As Variant, yin As Variant, yt As Variant) As Double Icol = 4 'column for dumping interpolated data intoĭo While ActiveSheet.Cells(irow, icol) '' Yin(c) = Cells(c + FirstRow - 1, 2).Value Xin(c) = Cells(c + FirstRow - 1, 1).Value ' Columns A and B have the data you are fitting ' Find last row of data, helps you figure out how many data points to read. (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)ĭim xin() As Double, yin() As Double, cspline As Double, xinterp As Doubleĭim yt() As Double, u() As Double, y As Double, dydx As Double, dydx2 As Doubleĭim c As Long, Ctr As Long, FirstRow As Long, FinalRow As Long, irow As Longĭim n As Long, i As Long, k As Long 'these are loop counting integers (I tried this with y=x*x points, dy/dx and d2y/dx2 looked great away from the end points). 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. Warning! Don't trust the derivatives near the end points (that is, first and last points in the Col A). How about something more sophisticated? Here's a cubic spline routine I've adapted to give you first (dy/dx) andĭata in Excel worksheet looks like this.

How to plot a graph in excel and find its derivative