Cubic splines in excel - anyone done it ?
JonSwaney
We have an aging program for determining cubic splines of a data set
(basically a standard curve) and converting data points to concentrations.
It's running on an unreliable system - so if anyone has faced and solved
the cubic spline problem in excel before, i'ld really like to hear from
you :)
Bruce, I don't know if this is exactly what your looking for, but I wrote
a macro (actually two macros) to generate cubic interpolations as follows:
Dim SplineCurve As Variant
Dim cache(10), SplinePrep(10)
Public Function Spline(xvals, yvals) As Variant
countx = 0
For Each z In xvals
countx = countx + 1
Next
yp1 = (yvals(2) - yvals(1)) / (xvals(2) - xvals(1))
ypn = (yvals(countx) - yvals(countx - 1)) / (xvals(countx) -
xvals(countx - 1))
If yp1 > 1E+30 Then
SplinePrep(1) = 0
cache(1) = 0
Else
SplinePrep(1) = -0.5
cache(1) = (3 / (xvals(2) - xvals(1))) * ((yvals(2) - yvals(1)) /
(xvals(2) - xvals(1)) - yp1)
End If
For i = 2 To countx - 1
sig = (xvals(i) - xvals(i - 1)) / (xvals(i + 1) - xvals(i - 1))
p = sig * SplinePrep(i - 1) + 2
SplinePrep(i) = (sig - 1) / p
cache(i) = (yvals(i + 1) - yvals(i)) / (xvals(i + 1) - xvals(i)) -
(yvals(i) - yvals(i - 1)) / (xvals(i) - xvals(i - 1))
cache(i) = (6 * cache(i) / (xvals(i + 1) - xvals(i - 1)) - sig *
cache(i - 1)) / p
Next
If ypn > 1E+30 Then
qn = 0
un = 0
Else
qn = 0.5
un = (3 / (xvals(countx) - xvals(countx - 1))) * (ypn -
(yvals(countx) - yvals(countx - 1)) / (xvals(countx) - xvals(countx - 1)))
End If
SplinePrep(countx) = (un - qn * cache(countx - 1)) / (qn *
SplinePrep(countx - 1) + 1)
For k = countx - 1 To 1 Step -1
SplinePrep(k) = SplinePrep(k) * SplinePrep(k + 1) + cache(k)
Next
Spline = SplinePrep
End Function
Public Function Splint(xs, ys, x)
SplineCurve = Spline(xs, ys)
Findx = 0
countx = 0
For Each i In xs
countx = countx + 1
Next
For i = 1 To countx
If xs(i) <= x Then Findx = i
Next
A = (xs(Findx + 1) - x) / (xs(Findx + 1) - xs(Findx))
B = 1 - A
C = ((Power2(A, 3) - A) * Power2(xs(Findx + 1) - xs(Findx), 2)) / 6
D = ((Power2(B, 3) - B) * Power2(xs(Findx + 1) - xs(Findx), 2)) / 6
Splint = A * ys(Findx) + B * ys(Findx + 1) + C * SplineCurve(Findx) +
D * SplineCurve(Findx + 1)
End Function
Public Function Power2(num, exp)
result = 1
For i = 1 To exp
result = result * num
Next
Power2 = result
End Function
Syntax is =Splint(known x values, known y values, x value for which you
need a y value). Write me if your having a problem with it.
Rob Scott
> (BRUCE SINCLAIR) wrote:
>
> > We have an aging program for determining cubic splines of a data set
> > (basically a standard curve) and converting data points to concentrations.
> > It's running on an unreliable system - so if anyone has faced and solved
> > the cubic spline problem in excel before, i'ld really like to hear from
> > you :)
> >
The spline can be an n-1 polynomial. A cubic spline is the polynomial
y= const + B1x + B2x^2 + B3x^3 . . .
you can estimate the coefficients of the funxtion via Ordinary Least
Squares.
Just create the matrixes and the vector of coefficients is:
(X'X)-1 X'Y
(-1 means inverted)
Both inverse and transpose are excel functions. You don't need
to write a macro or use solver.
Rob
----
Hanfred u.Ellen Eschner
You wrote at 05.03.96 int /comp/apps/spreadsheets
about Re: Cubic splines in excel - anyone done it ?:
><... But for a calibration curve, I'd
><recommend fitting the data to a power series. For moderate curvature I
><find that a cubic, Y = ax^3+bx^2+cx+d, is fine.
>
>we used to use this method ... sadly, every so often the power series had
>a violent abberation in it and gave insane results - only for some
>datasets and then repeatably.
Two years ago i wrote a surface-fitting macro for Quattro-Pro (DOS) with a
bicubic spline-funktion about (max) 8*10 values, looked like
z(x,y) = a0 + a1*x + a2*x**2+...+a?*x*y**2+...+a16*x**3*y**3
i only got good results, after i transponed <?> x and y
into values of X and Y within the range of -1 to +1 by
e.g. X = (2*x-(xmax+xmin))/(ymax-xmin)
---
Ciao, >Hanfred...@t-online.de<
Hanfred > Fidonet: 2:246/8758.19 <