5. Interpolation

Examples of computing interpolation by cubic spline function using XLPack are shown below.

Example: Logarithm table

Mathematical tables such as logarithm table were used in past days.

The following table is the part of natural logarithm table.

Two methods using worksheet function WPchse, WPchfe and VBA subroutine Pchse, Pchfe are described below.

Solving problem with worksheet function

Enter the data (x_i, y_i) into the appropriate cells in the worksheet. x_i must be in increasing order. Then select N (= number of data points) cells where the spline coefficients d are to be entered.

Click fx in the formula bar. Then select WPchse worksheet function from category XLPack.

Supply appropriate values for the requested parameters N (number of data points) (= 8 for this example), X() (range of data x), and Y() (range of data y).

Then press Ctrl+Shift+Enter.

Enter values of argument x where the interpolated values are to be computed in the other cells (for example, n = 1.55, 1.65, …, 2.15). Then compute the function values for these arguments by using WPchfe worksheet function. WPchfe computes the function value from the input parameters Ne (number of evaluation points) (= 7 for this example), Xe() (range of point data to be evaluated), N (number of data points used by WPchse) (= 8 for this example), X() (range of data x used by WPchse), Y() (range of data y used by WPchse), and D() (range of spline coefficients computed by WPchse).

Now we obtained the values for the arguments which are not tabulated.

Although data points are equally spaced in this example, they may be unequally spaced. However, the interpolated values for the interval with large space may be less accurate than those for short intervals. The values for the arguments of outside the data points (extrapolated values) can be computed but may be less accurate.

Solving problem with VBA program

The following is an example code using VBA library subroutine Pchse and Pchfe.

Sub Start()
    Const NMax = 10
    Dim N As Long, Ne As Long, I As Long, Info As Long
    Dim X(NMax) As Double, Y(NMax) As Double, D(NMax) As Double
    Dim Xe(NMax) As Double, Fe(NMax) As Double
    '--- Input data
    N = 8
    For I = 0 To N - 1
        X(I) = Cells(4 + I, 1)
        Y(I) = Cells(4 + I, 2)
    Next
    '--- Compute coefficients of a cubic spline
    Call Pchse(N, X(), Y(), D(), Info)
    If Info <> 0 Then
        MsgBox "** Error in Pchse **  Info = " + Str(Info)
        Exit Sub
    End If
    '--- Compute interporated values
    Ne = N - 1
    For I = 0 To Ne - 1
        Xe(I) = Cells(4 + I, 4)
    Next
    Call Pchfe(N, X(), Y(), D(), Ne, Xe(), Fe(), Info)
    If Info <> 0 Then
        MsgBox "** Error in Pchfe **  Info = " + Str(Info)
        Exit Sub
    End If
    '--- Output result
    For I = 0 To Ne - 1
        Cells(4 + I, 5) = Fe(I)
    Next
End Sub

The following result was obtained by running this program.