EDUCBA

EDUCBA

MENUMENU
  • Blog
  • All Courses
  • All in One Bundle
  • Login
Home Excel Excel Resources Statistical Functions in Excel Interpolate in Excel

Interpolate in Excel

Madhuri Thakur
Article byMadhuri Thakur

Updated July 21, 2023

Interpolate in Excel

Excel Interpolate (Table of Contents)

  • Introduction to Interpolate in Excel
    • Interpolation Methods
    • Simple Mathematics Formula
    • FORECAST Function
    • FORECAST Function along with OFFSET and MATCH Function
    • LINEAR Function
    • TREND Function
    • INTERCEPT Function
    • XLOOKUP Function
    • Examples of Interpolate in Excel

Introduction to Interpolate in Excel

Interpolating in Excel helps you estimate a value between two known values. For example, you have data on the number of social media followers and know the followers count in week 1 and week 3. In that case, you can use interpolation to estimate the followers in week 2.

This kind of forecasting is useful in many kinds of analysis, like investing, establishing strategies, insurance decisions, price movements, stocks, share markets, etc.

Excel has many functions, including “INTERPOLATE,” to help you do this. This article will look at different methods to interpolate in Excel.

Interpolation Methods

1. Simple Mathematics Formula

A simple mathematical formula is a mathematical equation used to represent a relationship between two or more variables. It is a concise and efficient way to describe a mathematical concept or relationship. It can solve problems in various fields, such as science, engineering, finance, and economics.
So, the simple formula for interpolation is:

y = y1 + (x – x1) * (y2 – y1) / (x2 – x1)

Where:

  • x1 and x2 are the known values of the independent variable that represent the unknown value of x that needs a functional value.
  • y1 and y2 are the corresponding values of the dependent variable for x1 and x2, respectively.
  • x represents the independent variable value for which we want to estimate the corresponding value of the dependent variable, y.

This formula finds the equation of a straight line passing through the known points (x1, y1) and (x2, y2) and then uses this line to compute the function value at the given value of x. The estimate’s accuracy depends on the degree to which the interpolated function is linear between the known points.

2. FORECAST Function

The FORECAST function estimates a value based on existing values and a linear trend. It is more convenient as it does not require memorizing any formula.
It has the following syntax:

FORECAST(x, known_y’s, known_x’s)

where,

  • x: the value whose corresponding value (y) we must predict.
  • known_y’s a known range of y values.
  • known_x’s: known range of x values..

3. FORECAST Function along with OFFSET and MATCH Function

While the Forecast Function is useful for estimating missing values in data, combining it with the OFFSET and MATCH functions can lead to more accurate results.
The syntax for this function is:

=FORECAST(x_value, OFFSET(y_range, MATCH(x_value, x_range, 1)-1, 0), OFFSET(x_range, MATCH(x_value, x_range, 1)-1, 0))

4. FORECAST.LINEAR Function

If you use Office 365, you will have access to the FORECAST.LINEAR function. FORECAST. The LINEAR function can handle multiple independent variables, whereas the Forecast function only works with a single independent variable.

When you use FORECAST, the LINEAR function in Excel assumes that changes in the independent variable (x) can be used to predict changes in the dependent variable (y). This means that if you have a set of known values for both x and y, you can use the function to make predictions about future values of y based on changes in x.

5. TREND Function

The TREND function in Excel helps you find a straight line that fits a set of data points and can even help you predict future values based on that line. Returning an array of y-values corresponding to the x-values can give you a sense of how the data trends over time.
This function is particularly handy for finding missing data points outside the range of your existing data.
The syntax of the TREND function is

=TREND(known_y’s, [known_x’s], [new_x’s], [const])

6. INTERCEPT Function

The INTERCEPT function in Excel calculates the y-intercept of a linear regression line that includes a set of data points. It gives the y-intercept value where the linear regression line intersects the y-axis.
This function can be really useful when the x-value is missing. Using the available data points, the INTERCEPT function can still accurately estimate where the regression line intersects the y-axis, even if you don’t have a specific value for x.
The syntax for the INTERCEPT function is as follows:

=INTERCEPT(known_y’s, known_x’s)

The y-intercept is the point where the regression line crosses the y-axis and represents the value of y when x is zero.

7. XLOOKUP Function

XLOOKUP function allows the search for a value in a particular range and returns a related value from another range. It is similar to the VLOOKUP and HLOOKUP functions but delivers more flexibility. To use XLOOKUP for interpolation, set up your data in two separate ranges, with the x values in one range and the y values in another. Then you could use a formula like LINEST, TREND, or FORECAST to interpolate the missing value.
The syntax for XLOOKUP:

=XLOOKUP(lookup_value, lookup_array, return_array, [match_mode], [search_mode])

Examples of Interpolate in Excel

After understanding the functions for interpolation, let’s explore how to apply them with the help of some examples.

You can download this Interpolate Excel Template here – Interpolate Excel Template

Example #1 Using Simple Mathematics Formula

We have a dataset with two known values of x and y. We want to estimate the value of y if x=22.5 using interpolation.
Interpolate In Excel-Example 1

Solution:
Step 1:  Select Cell E2
Step 2: We will apply the simple mathematics formula y = y1 + (x – x1) * (y2 – y1) / (x2 – x1) to find the corresponding y-value.
In this example, we will add the following formula in cell E2:

= B2 +(E1–A2) *((B3–B2)/(A3–A2))

where,
y1 = B2, x = E1,  x1 = A2, y2 = B3, y1 = B2, x2 = A3, and x1 = A2.
Example 1 Solution Step 2

Step 3: Press Enter to get the interpolated y-value.
Example 1 Solution Step 3

Result: We get the interpolated y-value as 64.1667.

There can be times when it becomes difficult to memorize the formula. The FORECAST function can be useful in such cases.

Example #2 Using FORECAST Function

Using the Excel Forecast Function, we can interpolate the same value in Example 1.
Interpolate In Excel-Example 2

Solution:
Step 1: Select Cell E2
Step 2: Enter the formula:

=FORECAST(E1,B2:B6,A2:A6)

Example 2 Solution Step 2

Explanation of formula:
In the formula FORECAST(x, known_y’s, known_x’s), x-value = 22.5 (in cell E1), known y’s = B2:B6, known x’s = A2:A6

Result: When we apply the Forecast Function instead of a complicated formula, it gives us the same result, i.e., 64.1667.

Interpolate In Excel-Example 2 Explination Formula

So we can see in the above image that the FORECAST function also works well for this purpose.

Example #3 Using the FORECAST with OFFSET Function

Consider the following dataset for a retail firm displaying the number of units sold daily. The sales values are presented linearly, and we need to estimate the sales for a new period of 28 days. The dataset already contains the sales figures for known periods of 10, 15, 20, 25, 30, 35, and 40 days, so we will use the FORECAST and OFFSET functions together to create an interpolation formula. The objective of the formula is to accurately estimate the sales value for this new period of 28 days by utilizing the known sales data.
Example 3

Solution:
The FORECAST function will perform linear regression on the given data set and predict the sales value for the new period of 28 days based on the relationship between the Days and Sales variables in the data set. The OFFSET function will select the relevant data ranges for the forecast calculation.

Step 1: Locate the x-value, i.e., 28 days in cell D2.
Step 2: Enter the formula in cell E2:

=FORECAST(D2,OFFSET(B2:B8,MATCH(D2,A2:A8,1)-1,0),OFFSET(A2:A8,MATCH(D2,A2:A8,1)-1,0))

Interpolate In Excel-Example 3 Solution Step 2

Let us understand how the formula works in this example:

  1. MATCH(D2, A2:A8, 1)-1: Finds the position of the largest value in the Days column that is less than or equal to the forecasted number of days (D2). The “-1” gets subtracted to adjust because the OFFSET function starts counting from 0.
  2. OFFSET(B2:B8, MATCH(D2, A2:A8, 1)-1, 0): Selects the Sales values corresponding to the days’ range in the linear regression analysis. The starting point of the range is B2, and the MATCH function determines the number of rows to move down.
  3. OFFSET(A2:A8, MATCH(D2, A2:A8, 1)-1, 0): Select the Days values corresponding to the days’ range in the linear regression analysis. The starting point of the range is A2, and the MATCH function determines the number of rows to move down.
  4. FORECAST(D2, OFFSET(B2:B8, MATCH(D2, A2:A8, 1)-1, 0), OFFSET(A2:A8, MATCH(D2, A2:A8, 1)-1, 0)): Takes the forecasted number of days (D2) and the ranges of Sales and Days values selected by the OFFSET functions as arguments to estimate the expected Sales value for the given number of days.

Example 3 Solution Explain Formula

Result: For 28 days, the forecasted sales will be 1120.

Example #4 Using the Forecast.linear Function

We have a dataset of the graphics department in a publishing company about the working hours of 30 employees over a span of 25 days, with data points available for every 5th day (i.e., on days 10, 15, 20, and 25). To estimate the working hours for any number of days between 1 and 25, we can use FORECAST.LINEAR function to perform the interpolation. For instance, we will apply the function to determine the working hours for 14 days based on the available data points.
Interpolate In Excel-Example 4

Solution:
Step 1: To calculate the working hours for 14 days, locate this value in B7.
Example 4 Step 1

Step 2: Enter the formula in cell B8:

=FORECAST.LINEAR(B7,B2:B5,A2:A5)

where,
B7 – x value that can change as per the need.
B2:B5 – the range of working hours, i.e., known y’s
A2:A5 – the range of days, i.e., known x’s

Note: As we calculate working hours for certain days, working hours are the dependent variables.

Interpolate In Excel-Example 4 Step 2

Result: The formula gives the result as 21.32.

Example 4 Step 2 Result

Thus, the working hours for 14 days could be 21.32, i.e., 21 hours, 19 minutes, and 12 seconds.
Similarly, we can find the working hours for any number of days between 1 to 25.

Example #5 Using Trend Function

We have a dataset about the distance a vehicle covers in a certain period. The data present shows the time taken for every increasing 5 km. For instance, the vehicle covers a distance of 5 km in 12 mins and 25 km in 40 mins. Using the Trend Function, find the time required for any distance covered beyond 25 km.
Example 5

Solution:
Step 1: Consider 36, 49, and 72 km (the new x’s) as the desired new data from A7:A9.
Example 5 Step 1

Step 2: We will input two formulas to apply the Trend Function Formulas in cell B7 with varying fourth arguments.

The const argument is set to FALSE in the first formula, =TREND(B2:B6, A2:A6, A7:A9, FALSE). This means the trend will be according to distance (x) from 0 km. 
Interpolate In Excel-Example 5 Step 2

The result of the first formula would be:
Example 5 Step 2 Result

If we don’t want to consider the data from the origin (i.e., 0 KM), we can use the ‘cont’ as TRUE.

This can be beneficial in cases where the data does not naturally pass through the origin, or there is a valid reason to assume that the trendline should not pass through the origin.

Note: There is no correct or incorrect approach to achieving this goal. It depends on the user whether to consider the origin or not.

Step 3:
We will apply the second formula, =TREND(B2:B6, A2:A6, A7:A9, TRUE), where the const argument is set to TRUE. This means that the y-intercept is not forced to be zero. Thus, Excel will ignore the origin and calculate the value normally per the data.
Example 5 Step 3

The result of the second formula would be:
Example 5 Step 3 result

The distance covered will differ depending on the choice of the function.

Example #6 Using the Intercept Function

The NGO has collected donations over 28 days and has recorded the amount received at the end of each week. The data shows the number of donations starting from the 7th day. We need to determine if the NGO had any previous donations, i.e., donations on Day 1 (before starting this campaign). Using the Intercept Function, let’s see how we can analyze this.
Example 6

Solution:
Step 1: Select cell B7 as the location to perform the function.
Step 2: Enter the formula in cell B7:

=INTERCEPT(B2:B5,A2:A5)

Interpolate In Excel-Example 6 Step 2

Note: The Intercept Function is useful when the x-value is missing.

Step 3: Press Enter after selecting the y’s and x’s range.
Example 6 Step 3

Result: The y-intercept for the above data of variables is 108.5.

Hence, the amount of donations before starting this charity campaign, i.e., on Day 1, is $108500.
The INTERCEPT Function considers a linear relationship between the Days and Donation ($) and calculates the y-intercept suitable considering the x-value as 0.

Example #7 Using the XLOOKUP Function

We have the structure of equated monthly installments (EMI) for a loan of $20000 at a 5% interest rate for a combination of years. The task is to find the EMI options for the desired years (x-coordinate) using XLOOKUP and FORECAST functions.
Example 7

Solution:
We need to determine the values of x1, x2, y1, and y2 for a desired x-value.
Step 1: Locate the x-coordinate in cell B9 and the values we need to find can be in cells G2 to G5.
Example 7 Step 1

We will have 8 as the x-coordinate, which can differ per the user’s requirements.
Step 2: Now, to find the value for x1, enter the following formula in cell G2 –
=XLOOKUP(B9,C2:C7,C2:C7,,-1,1)
Interpolate In Excel-Example 7 Step 2

Here, the lookup and return array is the same as we are looking for x1. -1 as match_mode refers to searching for an exact match or a smaller value in the range.
We receive the value of x1 as 7 in cell G2.

Step 3: Similarly, we calculate x2 in cell B3 by taking the match_mode as 1 to find the exact match or the next higher value in the range.
Therefore, the formula will be
=XLOOKUP(B9,C2:C7,C2:C7,,1,1)
Excel will give the value of x2 as 9.
Interpolate In Excel-Example 7 Step 3

Step 4: Change the return_array to C2:C7 to determine y1 and y2 in cells G4 and G5 per the images below.
Therefore, the formulas will be
y1 =XLOOKUP(B9,C2:C7,D2:D7,,-1,1)
y2 =XLOOKUP(B9,C2:C7,D2:D7,,1,1)
Example 7 Step 4

Example 7 Step 4-2

Now that we know the required values, we can determine the y-intercept, i.e., Interpolated Value of Loan Amount for 8 years, using the Forecast Function.

Step 5: Enter the Forecast Function formula in cell B10

=FORECAST(B9,G4:G5,G2:G3)

Interpolate In Excel-Example 7 Step 5

Note: It is advisable to use the FORECAST.LINEAR Function if one uses Office 365.

Example 7 Step 5-2

Result: Thus, the EMI for 8 years at a 5% interest rate on the loan amount of $20000 would be $3135 per the above image.

Things to Remember About Interpolate in Excel

  • Linear Interpolation is not an accurate method in MS Excel; however, it is time-saving and fast.
  • Linear Interpolation can even predict values for rainfall, geographical data points, etc.
  • If the data is not linear, other methods that can be useful for interpolation are Polynomial Interpolation, Spline Interpolation, etc.
  • The FORECAST function can extrapolate or predict future values.

Frequently Asked Questions

Q1. What are Interpolation and Extrapolation in Excel?

Answer: 

Aspect

Interpolation

Extrapolation

Definition Interpolation estimates the value for missing data from a given range Extrapolation estimates the value for missing data outside a given range
Data Relation A smooth and continuous relationship between known data figures A continuation of the same pattern outside the given known data
Risk Less risky as it involves known data points Riskier as it involves a value outside the known data range
Excel Functions To Calculate
  1. FORECAST
  2. FORECAST.LINEAR
  3. TREND
  4. INTERCEPT
  5. INDEX/MATCH
  6. LINEST
  7. XLOOKUP
Excel does not offer any specific function for extrapolation. Some users may choose TREND or FORECAST, which could be complex without actual data.
Example Estimating the temperature at 3 pm from the given temperatures at 2 pm and 4 pm Estimating the temperature at 7 pm considering the given temperatures from 2 pm to 4 pm
Q2. What are the Uses of Data Interpolation?

Answer:
Missing Data: Interpolation can estimate missing data points in a dataset.
Smoothing Data: Interpolation can also smooth out noisy data by filling in gaps between known data points. It can reduce the impact of outliers or noise in the data.
Future Value Predictions: Interpolation can predict future values of a variable using the known data points.
Continuous Curves: Interpolation can create continuous curves or surfaces from scattered data points in computer graphics to create smooth animations.
Derivatives and Integrals: Interpolation can calculate the derivatives and integrals of a function if a function is not available in an explicit form.
Measurement and Adjustment: Interpolation can calculate and adjust instruments using known data points.

Q3. What is the Importance of Interpolation in Numerical Analysis?

Answer: Interpolation is crucial in scientific computing, engineering, and many other fields where accurate data modeling and prediction are important. Interpolation allows:

  • Creation of continuous functions that can predict values between known data points.
  • Data analysis, signal processing, and image processing for data smoothing and filtering.
  • Development of numerical methods for solving differential equations, optimization problems, and other mathematical models.
  • Construction of mathematical models for physical systems, such as weather forecasting, oceanography, and the study of material properties.
  • Data visualization for the creation of smooth curves and surfaces that can represent complex data sets.
  • Machine learning algorithms, where interpolation predicts missing values and creates accurate models of complex systems.
  • Development of mathematical models for financial analysis, such as pricing derivative securities and estimating risk.
  • Scientific research for comparing and analyzing complex data sets and helping researchers to make predictions and test hypotheses.

Recommended Articles

This is a guide to Interpolate in Excel. Here, we discuss how to interpolate in Excel using functions like FORECAST, LINEAR, TREND, and more. TREND Function. You can also go through our other suggested articles:

  1. Excel Shortcut For Merge Cells
  2. MATCH in Excel
  3. Standard Deviation in Excel
  4. Remove Duplicates in Excel
MICROSOFT POWER BI Course Bundle - 8 Courses in 1
34+ Hours of HD Videos
8 Courses
Verifiable Certificate of Completion
Lifetime Access
4.5
MS Excel & VBA for Data Science Course Bundle - 24 Courses in 1 | 10 Mock Tests
87+ Hours of HD Videos
24 Courses
10 Mock Tests & Quizzes
Verifiable Certificate of Completion
Lifetime Access
4.5
All-in-One Financial Analyst Masters Training Program Bundle - 550+ Courses | 300+ Mock Tests | 2000+ Hours | Lifetime |
2000+ Hours of HD Videos
43 Learning Paths
550+ Courses
Verifiable Certificate of Completion
Lifetime Access
4.9
All-in-One Data Science Bundle - 400+ Courses | 550+ Mock Tests | 2000+ Hours | Lifetime |
2000+ Hour of HD Videos
80 Learning Paths
400+ Courses
Verifiable Certificate of Completion
Lifetime Access
4.7
Primary Sidebar
Popular Course in this category
MS Excel Course Bundle - 36 Courses in 1 | 13 Mock Tests
 137+ Hours of HD Videos
36 Courses
13 Mock Tests & Quizzes
  Verifiable Certificate of Completion
  Lifetime Access
4.9
Price

View Course
Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign Up
  • Live Classes
  • Certificate from Top Institutions
  • Contact Us
  • Verifiable Certificate
  • Reviews
  • Terms and Conditions
  • Privacy Policy
  •  
Apps
  • iPhone & iPad
  • Android
Resources
  • Free Courses
  • Excel Charts
  • Excel Tips
  • All Tutorials
Certification Courses
  • All Courses
  • Excel VBA Course - All in One Bundle
  • VBA Course
  • Excel Data Analysis Course
  • Excel for Marketing Course
  • Excel for Finance Course
  • Excel for HR Training

ISO 10004:2018 & ISO 9001:2015 Certified

© 2023 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA

Download Interpolate Excel Template

Let’s Get Started

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

EDUCBA
Free Excel Course

Excel functions, formula, charts, formatting creating excel dashboard & others

By continuing above step, you agree to our Terms of Use and Privacy Policy.
*Please provide your correct email id. Login details for this Free course will be emailed to you

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA Login

Forgot Password?

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA

Download Interpolate Excel Template

EDUCBA

डाउनलोड Interpolate Excel Template

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more