Extrapolation is a mathematical method that’s used to predict 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.
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.
Extrapolation Data by the Forecast Function
If you need a function to predict your data without creating charts and graphs (Internal Link), 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 for 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.
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% 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.
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.
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.
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.