In this tutorial, you will learn how to use linear interpolation in Excel.
A technique for fitting curves with linear polynomials is linear interpolation. It facilitates the construction of new data points within the bounds of a discrete collection of existing data points. Consequently, the simplest method for estimating a channel from the vector of estimates for the given channel is linear interpolation. It is particularly helpful for a variety of mathematical and scientific applications, including market research, data forecasting, and data prediction.
Once ready, we’ll get started by utilizing real-world examples to show you how to use linear interpolation in Excel.
Table of Contents
Anatomy of Linear Interpolation
There are two primary methods for using linear interpolation in Excel. The first method involves putting the linear interpolation mathematical formula into a calculator. This equation is:
y = y1 + (x – x1) ⨯ (y2 – y1) / (x2 – x1)
X1, X2, Y1, and Y2 are all known values in this equation. You can determine the alternative coordinate for a given value by substituting a new value for either y or x.
Using the FORECAST function, which in Excel is defined as =FORECAST(x,known ys,known xs), is the second method for performing linear interpolation. These are the components of this function:
known ys: All of the known values for y are represented by this variable. You would enter the cell coordinates for every value for y, for instance, if your chart had 10 different x and y values.
known xs: variable is identical to the known ys field but represents all x values. Similar to the y coordinates, you must enter every known value of x while filling out this variable.
x: The value you’re looking for is represented by the variable x in this function. A linear interpolated y value is produced when a number is inserted for the x variable.
Use Linear Interpolation in Excel
Before we begin we will need a group of data to use linear interpolation in Excel.
Step 1
First, you need to have a clean and tidy group of data.
Step 2
For example, if you want to look for the y value for a new x value which is 20, you can use this formula =FORECAST(20,OFFSET(B2:B8,MATCH(20,A2:A8,1)-1,0,2), OFFSET(A2:A8,MATCH(20,A2:A8,1)-1,0,2)).
Step 3
Once you press Enter, your formula will return the y value for the new x value inputted by using interpolation.
