Interpolate in Excel

Excel Interpolate (Table of Contents)

• Introduction to Interpolate in Excel
• Examples of Interpolate in Excel

Introduction to Interpolate in Excel

Interpolation is a method used to estimate or find 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= y₁ + (x – x₁) *

Here x=22.5

So when we apply this formula to the given dataset, we get the interpolated y value as:

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, the 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 the 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 the 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 the OFFSET function and MATCH function:

OFFSET function returns a cell or range of cells specified in 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.

The 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, the 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, the MATCH function finds the first value that is exactly equal to the lookup_value. For the value -1, the 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 can be used to predict values in between the given ones 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 a downloadable excel template. You can also go through our other suggested articles –

1. Excel INDEX Function
2. Linear Interpolation in Excel
3. Linear Programming in Excel
4. FORECAST Formula in Excel