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.

cubic spline interpolation 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.


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.

Set up Data Model for Cubic Spline Interpolation

Read More: How to Do Polynomial Interpolation in Excel (With Easy Steps)


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.

Input Required Data into Cubic Spline Model

Read More: How to Interpolate Between Two Values in Excel (6 Ways)


Similar Readings


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.

Navigating the Developer tab to click Visual Basic

  • Later, click on

Insert → Module

Choose Insert followed by 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.

Utilize Excel VBA Code to Build a User-Defined Function

Read More: How to Do VLOOKUP and Interpolate in Excel (6 Ways)


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.

Determine Interpolate Y Value Using User-Defined Function in Excel

  • 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.

Using AutoFill Handle icon to apply the formula in the other cells

Read More: How to Interpolate in Excel Graph (6 Methods)


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.

Selecting three required columns to plot the chart

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

Going to the Insert tab to click on the Scatter Chart icon

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

Choosing the Scatter with Smooth Lines symbol

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

Display Chart Data for Cubic Spline Interpolation in Excel

Read More: How to Perform Exponential Interpolation in Excel (4 Easy Ways)


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.


Related Articles

Lutfor Rahman Shimanto

Lutfor Rahman Shimanto

Hi there! I am Lutfor Rahman Shimanto. I have completed my graduation in Information Technology from Jahangirnagar University. Currently, I am working as a technical content writer at ExcelDemy. You will find all my articles on Microsoft Excel on this site. Outside of work, I enjoy Chess a lot. I am a founding member of the Jahangirnagar University Chess Club and an internationally rated chess player.

We will be happy to hear your thoughts

Leave a reply

5 Excel Hacks You Never Knew

Genius tips to help you unlock Excel's hidden features

FREE EMAIL BONUS

ExcelDemy
Logo