Excel Interpolate (Table of Contents)
Introduction to Interpolate in Excel
Interpolation is a method that is used to estimate or find out a value between two known values on a line or curve. This kind of forecasting is used in many kinds of analysis, like investing in growth, forecasting numbers, establishing strategies, insurance decisions, price movements, stocks, and share markets, etc.
Linear Interpolation means estimating the future value of a certain variable based on current data. In MS-Excel, a straight line is created which connects two known values, and thereby future value is calculated using simple mathematics formula or using FORECAST function.
Examples to Interpolate in Excel
Let’s understand how to Interpolate in Excel with some examples.
Example #1 – Using Simple Mathematics Formula
Let us say we have a simple dataset of two known x and y values, and we wish to interpolate a value (i.e. find the corresponding y value for the x value) as follows:
So the simple formula that is used to interpolate this value is:
y= y1 + (x – x1) *
Here x=22.5
So when we apply this formula to the given dataset, we get the interpolated y value as:
4.8 (3,294 ratings)
View Course
So we can see in the above screenshot that we have interpolated a value with two known values of x and y’s. There can be times when it becomes difficult to memorize the formula. So, FORECAST function can be used in such cases.
Example #2 – Using FORECAST Function
Now let’s say we wish to interpolate the same value in Example1 using FORECAST function.
FORECAST function estimates a value based on existing values along with a linear trend. It has the following syntax:
FORECAST(x, known_y’s, known_x’s)
Where,
- x: This is the value or data point whose corresponding value we wish to interpolate or predict.
- known_y’s: This is the known range of y values.
- known_x’s: This is the known range of x values.
So let us now see in the below screenshot what happens when we apply this FORECAST function to interpolate the given x value:
So we can see in the above screenshot that FORECAST function also works well for this.
Example #3 – Using the Forecast Function
Now let us say we have a dataset of a retail firm, with a number of days and the corresponding sales by the firm in those days (i.e. a number of units sold in those days) as below:
The sales are linear in this case (which can also be verified manually or using a line graph). Now let us see how we use the FORECAST function when the known_y’s and known_x’s are calculated using OFFSET and MATCH function:
Let us first see the syntax of OFFSET function and MATCH function:
OFFSET function returns a cell or range of cells which are specified number of rows and columns, depending on the height and width in specified rows and columns. It has the following syntax:
OFFSET (reference, rows, cols, [height], [width])
Where
- reference: This is the starting point from where the count of rows and columns will start.
- rows: This is the number of rows to offset below the starting reference cell.
- cols: This is the number of columns to offset right from the starting reference cell.
- height: Optional; Out of the returned reference, this is the rows’ height.
- width: Optional; Out of the returned reference, this is the columns’ width.
MATCH function returns the relative position of a lookup value in a row, column, or table that matches the specified value in a specified order. It has the following syntax:
MATCH (lookup_value, lookup_array, [match_type])
Where,
- lookup_value: This is the value that needs to be matched or looked upon from the lookup_array.
- lookup_array: This is the array or range of cells in which the lookup_value is to be searched.
- match_type: Optional; this can take values 1, 0,-1.
The default value for this match_type is 1. For value 1, MATCH function will find the largest value that is less than or equal to the lookup_value, and the value should be in ascending order. For value 0, MATCH function finds the first value that is exactly equal to the lookup_value. For the value -1, function will find the smallest value that is greater than or equal to the lookup_value and the value should be in descending order.
Now if we wish to estimate the sales for let’s say 28 days, then we use these above functions as follows:
So the first OFFSET function used as the second parameter in the FORECAST function is used to select the known_y’s (dependent values, i.e. the sales).
The second OFFSET function used as the third parameter in the FORECAST function is used to select the known_x’s (independent values, i.e. the number of days).
The MATCH function used as a parameter in the OFFSET function is used to generate the position of value that is to be forecasted and so calculate the number of rows. Columns in MATCH function, i.e. the second parameter in it should be 0 as the dependent value is desired on the same column that is selected.
So for 28 days, we estimated or forecasted the sales of the firm to be 1120. Similarly, we can estimate the sales of the firm for a different number of days using this FORECAST function.
Things to Remember About Interpolate in Excel
- The process of deriving a simple function from a discrete values dataset so that the function passes through all the given values and thus can be used to predict values in between the given ones is called interpolation.
- It is used to determine what data might exist outside of the collected data.
- Linear Interpolation is not an accurate method in MS Excel, however, it is time-saving and fast.
- Linear Interpolation can even be used to predict values for rainfall, geographical data points, etc.
- In case the data is not linear, then some other methods that can be used for interpolation in such cases are: Polynomial Interpolation, Spline Interpolation, etc.
- FORECAST function can even be used to extrapolate or predict the future values.
Recommended Articles
This is a guide to Interpolate in Excel. Here we discuss How to Interpolate in Excel along with practical examples and downloadable excel template. You can also go through our other suggested articles –
