How to interpolate between two values in Microsoft Excel.
Interpolation is crucial in analyzing experiments and making predictions or determinations about event outcomes. By utilizing data from two distinct instances, we can effectively estimate the values that lie between those instances through the process of interpolation.
This article will briefly describe two methods of how to interpolate between two values in Excel.
In this scenario, we possess a data sheet containing X and Y coordinates, and our objective is to perform interpolation between two specific values.
Method 1: Utilizing FORECAST.LINEAR Function
It is considered to be the simplest of all methods to interpolate between two values when values are approximately linear. It calculates the dependent variable (Y) for a given independent variable (X) by fitting a straight line to the available data.
Its general formula is
FORECAST.LINEAR(x, known_y’s, known_x’s)
- x is a data point to predict a value for
- known_y’s is the range containing know y
- known_x’s is the range containing know x
Step 1 – Determine Which Value You Want to Interpolate
- Here we want to determine interpolating between the values 4 and 5 so we have chosen a value of 4.5.
Step 2 – Type the FORECAST.LINEAR Formula
- Type the formula in the cell where you want the interpolated value to be displayed.
=FORECAST.LINEAR(C10,B2:B9,A2:A9)
Step 3 – Hit Enter Key
- Hit enter key to implement the formula in the particular cell.
Method 2: Utilizing Simple Mathematics
This method is used when the relationship between X & Y is perfectly Linear.
General formula is
y=y1+(x-x1)*(y2-y1)/(x2-x1)
The last section ((y2-y1)/(x2-x1)) calculates how much the Y value moves whenever the X value moves by 1. The second section (x-x1) calculates how far the interpolated X is away from the first X.
Step 1 – Determine Which Value You Want to Interpolate
- Here again, we want to calculate the values by interpolation method between the values 4 and 5 so the value of 4.5 is chosen.
Step 2 – Insert the Formula
- Insert the following formula in the formula bar.
Step 3 – Hit Enter Key
- Hit enter key to implement the formula in the particular cell.