Extrapolation is a mathematical method that predicts beyond the distinct range by programming and expanding past known data. So it’s a type of Excel data analysis and visualization. In this tutorial, we’re going to learn how to extrapolate data in Excel.
Table of Content
- Extrapolation Formula
- How to Extrapolate a Graph by Trendline
- How to Extrapolate Nonlinear Data by Trendline
- Extrapolation Data by the Forecast Function
- Excel Trend Function
To extrapolate data by formula, we need to use two points of the linear chart that we plotted before.
- A(a, b)
- B(c, d)
The linear extrapolation formula is:
You can enter the formula according to two points of your data values and extrapolate the target value.
How to Extrapolate a Graph by Trendline
Extrapolating a graph by trendline helps you represent visual data trends. Here we’re going to learn how to add a trendline to our charts:
- Select the data range.
- Go to the Insert tab from the ribbon.
- From the chart section, click on the Line chart (you can pick up the Scatter chart too.)
- Click on the Chart Element icon and check the Trendline checkbox.
- Double-click on the trend line of the graph to open the Format Trendline pane and apply your custom setting.
How to Extrapolate Nonlinear Data by Trendline
When you have a nonlinear dataset, you need to detect the data change trend using a trendline and then forecast the desired value. Here’s how you can do it:
- Draw a scatter plot.
- Click on the Chart Element icon and check the Trendline checkbox.
- Double-click on the trend line of the graph to open the Format Trendline pane.
- Add different types of trendline (exponential, logarithmic, and polynomial) to the chart and check “Display R-squared value on chart” and “Display Equation on Chart” boxes.
To determine the best trendline look at the R-squared value. The highest R-squared value shows the best trendline for your data.
- put x in the equation shown in the chart.
Choose The Best Trendline
When you have a data set, you need to detect the data change trend and forecast them in a graph.
Point: Do not use trendlines for Radar, Pie, Doughnut, Bubble, and 3D graphs.
In Excel, we have six types of trendlines.
- Moving Average
When data values rise or fall at increasingly higher rates, and there are no zero or negative Y values, we use the Exponential trendline.
When your plotted data set is similar to a line, in other words, when the data is increasing or decreasing at a steady rate, use the Linear trendline.
When you have a swift data decrease or increase, use the Logarithmic trendline. The data could be negative or positive. Invalid for zero or negative X values.
Assume you have a large data set that is analyzing gains and losses. Fluctuation is the main reason for using this trendline.
This trendline is used when you compare measurements that increase at a specific rate. Invalid for zero or negative X values.
This trendline uses the average of the particular number of data points by the Period option.
Data Extrapolation by the Forecast Function
If you need a function to predict your data without creating charts and graphs in Excel, use the Excel Forecast function. The Forecast function helps you extrapolate numerical data over a linear trend. Also, you can extrapolate a periodical template or even extrapolate a sheet.
Here we’re going to learn how to use the Forecast.linear, and the Forecast.ETS functions and how to extrapolate a sheet.
Extrapolation adjudges that the relationship between known values will also apply to unknown values. This function helps you extrapolate data that contains two sets of numerical values which correspond to each other.
Below is the Syntax of the Forecast.Linear function:
=FORECAST.LINEAR(x؛ known Ys؛ known Xs)
Assume we have a set of data that shows the number of sales for nine-month. We need to predict sales for the next three months. To use this function, follow these steps:
- Select an empty cell.
- Enter the =forecast or the =forecast.linear in the Formula Bar.
- Click on the x value you want to predict for itself, and enter a semicolon or comma(according to your Excel version.)
- Select all known Ys, enter a semicolon, and then select all known Xs.
- Press Enter.
#N/A: If the size of the known_Ys and known_Xs is not equal, or if one or both are empty, This error will occur.
#DIV/0: If the variance of the known_Xs is equal to zero, this error will occur
#VALUE: If the input x is non-numeric, This error will occur. Click on the link if you want to know more about Excel formula errors and why they happen.
In some cases, you have a seasonal pattern, and this periodical template needs a particular function to forecast the future. Here we have a sales amount for a year, and we need to predict the first three months of the next year.
The syntax of the Forecast.ETS function is:
=FORECAST.ETS(target_date؛ values؛ timeline؛ [seasonality], [data_complation]; [aggregation])
Target_date: The point you need to forecast.
Values: Here are all known sales amounts.
Timeline: In this case, the number of months.
[seasonality]: The length of the seasonal pattern (optional argument.)
[data_complation]: Although the timeline requires a constant step between data points, FORECAST.ETS supports up to 30% of missing data and will automatically adjust for it (optional argument.)
[aggregation]: The aggregation parameter is a numeric value indicating which method will be used to aggregate several values with the same timestamp (optional argument.)
Now follow these steps to forecast your target values:
- Select an empty cell where you want to represent the result.
- Enter the syntax of the function and enter the arguments, as we mentioned.
- Press Enter.
#N/A: If the values and timeline arrays have different sizes, this error is returned
#VALUE: If any of the seasonality, data completion or aggregation arguments is non-numeric, this error will occur.
#NUM: This error will be returned if the seasonality exceeds 87600, the data completion value is anything other than 0 or 1, aggregation value is not valid (any non-integer number or out of 1-7 range), or the function cannot detect a consistent step size in the timeline. If you want to know more about Excel formula errors and why they happen, click on the link.
Excel 2016 and later versions provide a tool to forecast the sheet. This tool creates a table according to your data and determines lower and upper confidence bound.
To use the Forecast Sheet, go to the Data tab from the Forecast group, click on the Forecast Sheet tool to open the Create Forecast Worksheet box. You can pick a line chart or column chart by their icons in the top right corner of the box.
If you need to customize the forecast chart, you can edit by clicking on options:
- Where the Forecast starts or ends
- Change the confidence interval
- Add the Forecast statistics
- Change the Timeline and Values range
- And aggregate duplicate using
Then press the Create button and see the result.
Excel Trend Function
Another function to extrapolate data without plotting graphs is the Trend function in Excel. This statistical function is going to predict future trends according to the known values based on linear regression.
The syntax of the Trend function:
=TREND(known_Ys; [known_Xs]; [new_Xs]; [const])
Known Ys: The Y values we already know.
Known Xs: The X values we already know (optional argument.)
Const: according to Y=mX+b, if const is false, b is zero, but if const is true or skipped, b is calculated normally.
#REF!: If known_Xs and known_Ys arguments have different sizes, this error occurs
#VALUE: This error occurs if you enter a non-logical value as the const argument, or you enter a non-numeric value as the function’s other arguments. Click on the link if you want to know more about Excel formula errors and why they happen.
You can connect with us, ask our experts if you have any inquiries, and get more support via Excel Support Services.
Also, reduce costs, accelerate tasks, and improve quality with Excel Automation Services.
Generally, extrapolation is not so reliable because we cannot be sure that the data trend will continue out of our data range. In addition, there is nothing to check how accurate our prediction is. However, when our original data is very consistent, we can rely on extrapolation.
Regression is the mathematical process to find a line or curve that fits the data. Once you find the proper line or curve, you can predict the unknown value based on its equation.
When working with time series, both of them return the same result since they use the same mathematical method.
The difference between these two is that the FORECAST function works only as a regular formula returning one value as output. In contrast, the TREND function can be used as an array formula and calculate multiple y values corresponding to multiple x values.