Spline interpolation for Excel?

[Jim Martin]

Hello All:

Some time ago, I used the XLMath add-in of Roi Kari to perform a cubic
spline interpolation of an array of data. That add-in does not work with 32
bit operating systems, and so is no longer useful to me. I have tried using
the InterpolatingSpline add-in from the Kaler Group, but as far as I can
tell it simply does not function.

Can anyone suggest a source for an add-in that will do a cubic spline
interpolation? Specifically, my data is recorded in equal increments of
angular displacement and I need to interpolate it to equal increments of
time so that I can use a Butterworth filter. I currently do it with a set
of individual linear interpolations with lookup functions in a VERY messy
(and slow) way and would prefer to have a single array function.

Thanks in advance,

Jim

[SteveYandl]

Jim,

I just picked up the Standard version of Mathcad8 from MathSoft, Inc. and
looking through the manual, it will do a cubic spline interpolation and can
integrate with Excel. The program cost me about $100 which would be a bit
steep if this is all you need it for.

There web site is given as http://www.mathsoft.com/

Steve Yandl

--
Jim Martin <jcma...@sc.edu> wrote in message
news:7jjncc$10q$1...@news3.infoave.net...

[yos...@ccwf.cc.utexas.edu]

Jim Martin (jcma...@sc.edu) wrote:

: Some time ago, I used the XLMath add-in of Roi Kari to perform a cubic

: spline interpolation of an array of data. That add-in does not work with 32

Hi Jim,

If you don't minding poting c or fortran code, there is a short code (40
lines including comment) at
http://www.ulib.org/webRoot/Books/Numerical_Recipes/bookfpdf/f3-3.pdf
or
http://www.ulib.org/webRoot/Books/Numerical_Recipes/bookcpdf/c3-3.pdf

It's home page is www.nr.com, the Numerical Recipes.

--
---
yosuke kimura
grad. student (Environ. Wat. Resour. Engr.)
The Univ. of Texas at Austin, USA

[Harlan Grove]

In article <7jmpcu$p4l$2...@geraldo.cc.utexas.edu>,

yos...@ccwf.cc.utexas.edu () wrote:
> Jim Martin (jcma...@sc.edu) wrote:
>
>>Some time ago, I used the XLMath add-in of Roi Kari to perform a
>>cubic spline interpolation of an array of data. That add-in does
>>not work with 32
>

>If you don't minding poting c or fortran code, there is a
>short code (40 lines including comment) at
>http://www.ulib.org/webRoot/Books/Numerical_Recipes/bookfpdf/f3-3.pdf
> or
>http://www.ulib.org/webRoot/Books/Numerical_Recipes/bookcpdf/c3-3.pdf

Why Numerical Recipes in C is not my favorite programming book: after
the authors show that B = 1 - A near the beginning of section 3.3, they
proceed to calculate B as b=(x-xa[klo])/h rather than as b=1-a in their
splint() function. This is not the only oversight in the code presented
in the section on splines. They should have used pseudocode and
dispensed with separate C, Fortran and Pascal editions. Even so, the
algorithm works well - only the C code stinks.

Here's a VBA implementation with some modifications to the C code. I
use different function names: spline_D2y() generates an Nx1 array of
second derivatives of y at each x value; spline_y() evaluates the
spline interpolation at its x argument. I pass tabular x and y values
in a single Nx2 array t which relieves me of having to check that x and
y arrays are the same size; also, this eliminates having to copy Range
arguments to arrays in order to avoid subscripting errors.

In spline_D2y(), I handle the boundary conditions by making the first
derivative y values at the endpoints optional arguments rather than
relying on HUGE values as triggers for natural splines. In spline_y(),
I check that the x argument falls between the low and high x values in
the table (inclusive), and if not spline_y returns an error.

Option Base 1

Function spline_D2y(t As Variant, Optional Dy1 = Empty, _
Optional Dyn = Empty) As Variant
Dim D2y() As Double, u() As Double, sig As Double, p As Double
Dim dxb As Double, dxc As Double, dxf As Double
Dim dyb As Double, dyf As Double
Dim i As Integer, hi As Integer, lo As Integer

If TypeName(t) = "Range" Then t = t.Value

lo = LBound(t, 1)
hi = UBound(t, 1)
ReDim D2y(lo To hi, 1)
ReDim u(lo To hi)

dxf = t(lo + 1, 1) - t(lo, 1)
dyf = t(lo + 1, 2) - t(lo, 2)

If IsEmpty(Dy1) Then
D2y(lo, 1) = 0
u(lo) = 0
Else
D2y(lo, 1) = -0.5
u(lo) = 3 * (dyf / dxf - Dy1) / dxf
End If

For i = lo + 1 To hi - 1
dxb = dxf
dxc = t(i + 1, 1) - t(i - 1, 1)
dxf = t(i + 1, 1) - t(i, 1)
dyb = dyf
dyf = t(i + 1, 2) - t(i, 2)
sig = dxb / dxc
p = sig * D2y(i - 1, 1) + 2
D2y(i, 1) = (sig - 1) / p
u(i) = (6 * (dyf / dxf - dyb / dxb) / dxc - sig * u(i - 1)) / p
Next i

If IsEmpty(Dyn) Then
D2y(hi, 1) = 0
Else
u(hi) = 3 * (Dyn - dyf / dxf) / dxf
D2y(hi, 1) = (u(hi) - u(hi - 1) / 2) / (D2y(hi - 1, 1) / 2 + 1)
End If

For i = hi - 1 To lo Step -1
D2y(i, 1) = D2y(i, 1) * D2y(i + 1, 1) + u(i)
Next i

spline_D2y = D2y
End Function

Function spline_y(x As Double, t As Variant, D2y As Variant) _
As Variant
Dim h As Double, a As Double, b As Double
Dim lo As Integer, hi As Integer, mid As Integer

If TypeName(t) = "Range" Then t = t.Value
If TypeName(D2y) = "Range" Then D2y = D2y.Value

lo = LBound(t, 1)
hi = UBound(t, 1)

If x < t(lo, 1) Or x > t(hi, 1) Then
spline_y = CVErr(xlErrValue)
Exit Function
End If

Do
mid = (lo + hi) / 2
If t(mid, 1) > x Then hi = mid Else lo = mid
Loop While hi - lo > 1

h = t(hi, 1) - t(lo, 1)

If h = 0 Then
spline_y = CVErr(xlErrDiv0)
Exit Function
End If

a = (t(hi, 1) - x) / h
b = 1 - a
spline_y = a * t(lo, 2) + b * t(hi, 2) + _
((a ^ 3 - a) * D2y(lo, 1) + (b ^ 3 - b) * D2y(hi, 1)) * h ^ 2 / 6
End Function

Sent via Deja.com http://www.deja.com/
Share what you know. Learn what you don't.