How to Apply Cubic Spline Interpolation in Excel (with Easy Steps)

When it comes to software, Microsoft Excel is in a league of its own. Thanks to its many useful features, we may fully use any data. This article will cover how we can use Excel VBA to interpolate Cubic Spline from start to finish here. Cubic Spline Interpolation is a curve-fitting method to interpolate a smooth curve between discrete data points. We use this Interpolation in various applications due to its ability to model smooth and continuous curves that pass through all the data points while being computationally efficient and easy to implement. Keeping this in mind, we’ll look at the specific steps for using Cubic Spline Interpolation in Excel.

Download Practice Workbook

Please click the link below this section if you’d like a free copy of the sample workbook discussed in the presentation.

Cubic Spline Interpolation.xlsm

What Is Cubic Spline Interpolation?

Cubic Spline Interpolation is constructing a smooth curve that passes through a given set of data points. It uses a set of cubic polynomials to represent the curve, ensuring that the resulting curve is smooth and has continuous first and second derivatives. We need cubic spline interpolation because we often have a set of discrete data points that need to be transformed into a continuous function in real-world applications. Cubic spline interpolation provides a smooth curve representing this continuous function and making predictions or estimates at unavailable data.

Steps to Do Cubic Spline Interpolation in Excel

If we know the proper steps, it can be easy to interpolate Cubic Spline in Excel. This post will show how you can use the VBA language to display the Interpolation of Cubic Spline in 5 steps. In the first step, we will organize the Data Model. Later, we will insert the required value into the model. We’ll write VBA code throughout the following step to create a User-Defined function to determine the interpolated values. In the next step, we’ll discuss plotting the graph using the values produced from the user-defined function. Follow these steps carefully to figure out how to do something quickly.

Step 1: Set up Data Model for Cubic Spline Interpolation

The first and foremost step is to create a dataset for illustration purposes. In this article, we will consider the dataset having four columns titled X-Value, Y-Value, Target X and Interpolated Y. Please follow the steps below to make the model.

First, build two columns named X-Value and Y-Value throughout B and C.
Later, take another section called Target X in the D column.
Lastly, make the Interpolated Y column in E to see the model like the below one.

Step 2: Input Required Data into Cubic Spline Model

In this context, we will insert the necessary values into the model. Firstly, we input the X-Value and Y-Value columns. We also have to provide the targeted X value for the given X and Y value, and the Interpolated Y value will produce concerning the Target X column.

Initially, insert the intended values in the X-Values and Y-Values columns.
After that, input the desired values for the Target X column like the following.

Similar Readings

How to Calculate Logarithmic Interpolation in Excel (2 Easy Ways)
Do Interpolation with GROWTH & TREND Functions in Excel
How to Do Linear Interpolation in Excel (7 Handy Methods)

Step 3: Utilize Excel VBA Code to Build a User-Defined Function

The acronym VBA stands for Visual Basic for Application, and Microsoft created VBA as its programming language. Users can access Excel-incompatible functionalities by utilizing the VBA programming language. In this section, we will use the VBA to make a User-Defined function in Excel called CubicSpline. Please read the instructions carefully and follow them to accomplish the task.

First, navigate to the Developer tab.
Second, from the Code group, click on the Visual Basic symbol.

Later, click on

Insert → Module

Next, insert the following code in the Module box.

Function CubicSpline(xValues As Range, yValues As Range, X As Double) As Double
    Dim n As Integer
    Dim h() As Double
    Dim b() As Double
    Dim u() As Double
    Dim v() As Double
    Dim i As Integer
    n = xValues.Count
    ReDim h(1 To n – 1)
    ReDim b(1 To n)
    ReDim u(1 To n – 1)
    ReDim v(1 To n – 1)
    For i = 2 To n
        h(i – 1) = xValues(i) – xValues(i – 1)
    Next i
    For i = 2 To n – 1
        b(i) = 3 * ((yValues(i + 1) – yValues(i)) / h(i) – (yValues(i) – yValues(i – 1)) / h(i – 1))
    Next i
    u(1) = 2 * h(1)
    v(1) = b(2)
    For i = 2 To n – 2
        u(i) = 2 * (h(i) + h(i – 1)) – h(i – 1) ^ 2 / u(i – 1)
        v(i) = b(i + 1) – h(i – 1) * v(i – 1) / u(i – 1)
    Next i
    For i = n – 2 To 1 Step -1
        b(i) = (v(i) – h(i) * b(i + 1)) / u(i)
    Next i
    b(n) = 0
    CubicSpline = yValues(1)
    For i = 2 To n
        If X <= xValues(i) Then
            CubicSpline = yValues(i – 1) + (X – xValues(i – 1)) * (b(i – 1) + 2 * b(i) + (X – xValues(i)) * (3 * b(i) / h(i – 1) – b(i – 1) – b(i)) / h(i – 1)) / 3
            Exit For
        End If
    Next i
    CubicSpline = CubicSpline
End Function

Now, press Ctrl + S or click the Save icon.

Step 4: Determine Interpolate Y Value Using User-Defined Function in Excel

At this point, we will call the function we previously developed and determine the Interpolated Y value to plot a smooth graph. Please read the directions thoroughly and stick to them to complete the work.

To begin, select the E5 cell.
Second, input the equation below in the Formula bar.

=CubicSpline($B$5:$B$10,$C$5:$C$10,D5)

After that, hit the Enter or Tab key to see the result.

We must use the same formula in the other cells at this stage.
To achieve this, drag the AutoFill Handle icon and move it to the E10
As a result, we get the desired output like the below one.

Step 5: Display Chart Data for Cubic Spline Interpolation in Excel

Finally, after getting the Interpolated Y values, we can plot the intended graph. Here, we will consider the Scatter with Smooth Lines to graph the values.

To begin, select the X-Value, Y-Value and Interpolated Y columns.

After that, go to the Insert tab.
From the Charts group, now click on the Scatter Chart symbol.

Subsequently, a display bar will open and choose the Scatter with Smooth Lines.

As a result, it will display the Cubic Spline Interpolation like the following.

Things to Remember

The User-Defined function can malfunction if the Target X columns contain a value that crosses the upper and lower boundaries of the X-Value.

Conclusion

Following the below instructions will allow you to use the Cubic Spline Interpolation in Excel. The ExcelDemy website offers further relevant papers. Please share any additional recommendations or enhanced methods as you continue to apply them. Include your opinions, questions, and requests in the allocated area.

How to Do Linear Interpolation Excel VBA (with Easy Steps)
Interpolate Time Series in Excel (3 Easy Ways)
How to Do 2D Interpolation in Excel (2 Suitable Ways)
Perform Bilinear Interpolation in Excel (with Easy Steps)

The post How to Apply Cubic Spline Interpolation in Excel (with Easy Steps) appeared first on ExcelDemy.