How to Perform Linear Extrapolation in Excel (with Easy Steps)

Get FREE Advanced Excel Exercises with Solutions!

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.


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.


How to Perform Linear Extrapolation in Excel: with Easy Steps

Consider the following sample data. Where we have several sets of values (X1,Y1), (X2,Y2)….(X10,Y10).

Linear Extrapolation Sample Data

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)

Applying Linear Extrapolation Formula

  • 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.

Download Practice Workbook

Download the following practice workbook so that you can practice while reading this article.


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!


Related Articles


<< Go Back to Excel Extrapolation | Excel for Statistics | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Masum Mahdy
Masum Mahdy

Abu Talha Masum Mahdy (Assistant Project Manager, ExcelDemy) has been working with the ExcelDemy family for over 2 years. He wrote 30+ articles and reviewed more than 1300 others for ExcelDemy. Currently, he is responsible for reviewing articles from 2 writing teams of ExcelDemy. He ensures correctness and fluency in his team write-ups. Mahdy holds a BSc in Naval Architecture and Marine Engineering from BUET. His interests are Project Management, Content Writing, SEO, Digital Marketing, Data Analytics and... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo