Interpolation in Excel
Interpolation is the forecasting technique to predict future patterns and values based on the available past data or current data points. Interpolation in excel helps us to find the value between two points on a line of graph or line of the curve. In simple words “Inter” suggests that look inside the data that we already have. Not only in statistics but also in the field of Science, Commerce, business it is used to find or predict the future value which falls between two existing data points.
Example of Data Interpolation in Excel
In order to understand the concept of Data Interpolation in excel let’s look at the below example. In a farmhouse, a farmer is growing paddy and he keeps tracking the growth of the paddy.
The farmer has recorded below-trend growth of the paddy in 20 days where he has recorded growth once in every 4 days.
From the above table, a farmer wants to know how tall paddy was on the 5th day.
By looking at the data we can easily estimate that on 5th-day paddy was at 2.5 inches. The reason why we can easily tell the growth of the paddy crop because it has grown in linear pattern i.e. there was a relationship between the number of days recorded and inches paddy has grown. Below is the graph to show the linear growth of the paddy.
The above graph easily shows the linear growth pattern of the paddy. But if the paddy has grown in linear pattern then it is tough to predict the 5th-day growth.
Based on this above curve farmers cannot estimate what was the 5th-day growth. So, this is where our Interpolation concept helps us to find the growth on the 5th day.
For interpolation, we have a below formula.
Here we have two variables i.e. X1 & Y1. “X” is the first set of values and “Y” is the second set of values.
In our example of paddy growth first set of value is (4,2). Here “4” is the day and “2” is the growth inches of the paddy.
The second set of values is (8,4). Here “8” is the day and “4” is the growth inches of the paddy.
Since we need to find the growth on the 5th-day variable “x” becomes 5 to the growth inches variable “y”.
So let’s apply the values to the above formula.
Now do the first step calculation.
Note “x” is equal to
So, on 5th-day paddy growth would be at 2.5 inches.
Linear Data Interpolation in Excel
The same Linear interpolation can be done in excel as well. Take the same data to the excel as well.
Now we need to find the growth inches for 5th day, so x = 5.
Set 1 (x1, y1)
Set 2 (x2, y2)
So x1 = 4, y1 = 2, x2 = 8, and y2 = 4.
Enter these values in excel sheet cells.
I have mentioned question marks for x1, y1, x2, and y2. Because with this simple data we can easily find with just our eyes. But it is always a good practice to find these values through the formula. To find the “x1” value apply the below formula.
Now to find the “y1” value apply the below formula.
To find the “x2” value apply the below formula.
To find the “y2” value apply the below formula.
Like this using these formulas, we can find the values for all the parameters of the Interpolation in excel formula.
Next to find the 5th-day paddy growth inches apply the below formula.
So, as we have calculated manually with formula also we got 2.5 as the answer. If needed we can insert the line graph for the data.
Things to Remember Here
- Interpolation in excel is the process of finding the middle value of the existing data.
- There is no built-in formula in excel to calculate the excel Interpolation value.
- In MATCH function we need to use “1” for the parameter “match type” which helps users to find the value that is greater than the lookup value.
This has been a guide to Interpolation in Excel. Here we discuss how to do Data Interpolation in Excel along with examples and downloadable excel template. You may also look at these useful functions in excel –