Extrapolation is the process of using historical data values to predict future values.
The easiest way to extrapolate data in Excel is by using the TREND function, which uses the following syntax:
TREND(known_y’s, [known_x’s], [new_x’s], [const])
where:
- known_y’s: The range of known y-values
- known_x’s: The range of known x-values
- new_x’s: The range of new x-values
- const: Whether to force the constant in the trendline equation to be zero.
Note: The TREND function is designed to be used with data that follows a linear trend. For datasets that exhibit a nonlinear trend, you should use more advanced techniques.
The following step-by-step example shows how to use the TREND function to extrapolate data in practice in Excel.
Step 1: Create the Dataset
First, let’s enter the values for the following dataset into Excel:
Step 2: Check if the Data Follows a Linear Trend
Next, let’s create a scatter plot to check if the data follows a linear trend.
To do so, highlight the range A2:B14, then click the Insert tab along the top ribbon, then click the Scatter icon in the Charts group:
The following scatter plot will appear:
From the plot we can see that the points roughly follow a linear trend.
In other words, the points fall roughly along a straight diagonal line.
Thus, we can proceed to use the TREND function to extrapolate future data values.
Note: If the data exhibits a nonlinear pattern, you may try polynomial curve fitting instead.
Step 3: Use TREND Function to Extrapolate New Values
Suppose we would like to use our existing data to extrapolate the y-values for the following new x-values:
- x = 25
- x = 30
- x = 35
To do so, we can define these new values in our worksheet and then type the following formula into cell B17:
=TREND($B$2:$B$14, $A$2:$A$14, A17)
We can then click and drag this formula down to each of the new values:
Here is how to interpret the output:
- For a value of x = 25, the forecasted value of y is 26.75229.
- For a value of x = 30, the forecasted value of y is 31.596.
- For a value of x = 35, the forecasted value of y is 36.43971.
If you would like to know the exact formula used to extrapolate these values, you can type the following formula into cell D2:
=LINEST(A2:A14, B2:B14)
The following screenshot shows how to use this formula in practice:
The LINEST function calculates the linear trendline for the existing data, which turns out to be:
y = 0.968741x + 2.533759
The TREND function uses this equation under the hood to extrapolate the y-values.
For example, for a value of x = 25, the equation calculates the following:
y = 0.968741*(25) + 2.533759 = 26.75229
This matches the value calculated by the TREND function.
Additional Resources
The following tutorials explain how to perform other common tasks in Excel:
How to Create a Sales Forecast in Excel
How to Create a Line of Best Fit in Excel
How to Add a Quadratic Trendline in Excel
