Masum Mahdy
Extrapolation is known as extending a current trend into the future or applying sample study findings to the entire population. In this article, we will show how to perform linear extrapolation in Excel with quick steps with an example.
Download Practice Workbook
Download the following practice workbook so that you can practice while reading this article.
What Is Linear Extrapolation Formula?
Linear Extrapolation is used to compare two series and determine an unknown number that is numerically outside the bounds of one set of values but is in direct linear proportion to another set of known values.
The following formula is used to perform linear extrapolation,
y(x) = y1+ (x- x1)/(x2-x1) ✕ (y2-y1)Where, (x1,y1) and (x2,y2) are two known points in the linear graph, and (x,y) is the point at which you want to extrapolate. (x,y) lies outside (x1,y1) and (x2,y2).
If you know, the value of x, you can find the value of y using this equation.
Now, we will see how to perform linear extrapolation step by step using this equation, with an example in the next section.
Read More: How to Extrapolate Data in Excel (5 Handy Ways)
Steps to Perform Linear Extrapolation in Excel
Consider the following sample data. Where we have several sets of values (X1,Y1), (X2,Y2)….(X10,Y10).
Given X11=11, we will extrapolate the value of Y11 using linear extrapolation.
Step 1: Set the Value of Independent Variable and Find the End Points in the Linear Data [(x1,y1) and (x2,y2) Pair in the Formula]
- In this data, the closest values of X11 and Y11 are (9,25) and (10,28).
- So, in the linear extrapolation formula,
- x1=9 in cell C13
- y1=25 in cell E13
- x2=10 in cell C14
- y2=28 in cell E14
- x=11 in cell C15
Step 2: Apply the Linear Extrapolation Formula to Find the Value of Dependent Variable
- Now, apply the formula to find the value of Y11 at X11=11.
- Insert the following formula in cell E15:
=E13+(C15-C13)/(C14-C13)*(E14-E13)
- Press Enter and you will get the result!
Several Alternatives to Linear Extrapolation Formula in Excel
If you want to use Excel functions instead, you can use forecast functions like FORECAST, FORECAST.LINEAR, TREND, etc.
- For the FORECAST function, the formula will be as follows.
=FORECAST(C15,E5:E14,C5:C14)- Here, C15= x
- E5:E14= known_ys
- C5:C14= known_xs
- If you use the FORECAST.LINEAR function, the formula will be,
=FORECAST.LINEAR(C15,E5:E14,C5:C14)- For the TREND function, the following formula will go,
=TREND(E5:E14,C5:C14,C15,TRUE)- Here, E5:E14= known_ys
- C5:C14= known_xs
- C5=new_xs
- TRUE is the value of constant b set to calculate normally.
Things to Remember
- One must first study the data to find out if the data is following the trend and whether one can forecast the same.
- Linear extrapolation needs two variables, dependent and independent.
- Excel functions return different extrapolation results because they consider all historical x and y values in the dataset.
Conclusion
These were the steps of linear extrapolation in Excel with some alternatives. Was this article helpful? Please leave us a comment with your feedback!
