Lutfor Rahman Shimanto
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.
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.
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.
Read More: How to Interpolate Between Two Values in Excel (6 Ways)
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.
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.
- 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.
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.
- 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.
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.
