Excel Functions Tutorials
- Excel Tips
- Excel vs Access
- Excel Rows vs Columns
- Apple Numbers vs Excel
- 3D Reference in Excel
- Absolute Reference in Excel
- Mixed References in Excel
- Excel Reference to Another Sheet
- Array Formulas in Excel
- Arrays in Excel VBA
- Auto Numbering in Excel
- AutoFit in Excel
- AutoCorrect in Excel
- AutoSave in Excel
- AutoRecover in Excel
- Bullet Points in Excel
- Break Links in Excel
- Barcode in Excel
- Change Case in Excel
- CAGR Formula in Excel
- Calculate Age in Excel
- Calculate Percentage in Excel Formula
- Cell Reference in Excel
- Checklist in Excel
- Circular Reference in Excel
- Column Sort in Excel
- Column Lock in Excel
- Move Columns in Excel
- Custom List in Excel
- Consolidate in Excel
- Combine Cells in Excel
- Compare Two Columns in Excel
- Compare and Match Columns in Excel
- Compound Interest Formula in Excel
- Convert Columns to Rows in Excel
- Convert Date to Text in Excel
- Convert Numbers to Text in Excel
- Convert Text to Numbers in Excel
- Convert Excel to CSV
- Count Characters in Excel
- Count Rows in Excel
- Count Unique Values in Excel
- Countif not Blank in Excel
- Create Templates in Excel
- Family Tree in Excel Template
- Custom Number Format in Excel
- Delete Row Shortcut in Excel
- Divide in Excel Formula
- Drop Down List in Excel
- Dynamic Tables in Excel
- Dashboard in Excel
- KPI Dashboard in Excel
- Date to Text in Excel
- Date Format in Excel
- Database in Excel
- Delta Symbol in Excel
- $ Symbol in Excel
- Excel Column to Number
- Edit Drop-Down List in Excel
- Equations in Excel
- Exponents in Excel
- Excel Extensions
- Excel Translate
- Excel Not Responding
- Excel Find and Replace
- Find and Select in Excel
- Excel Subtraction Formula
- Excel Formula for Grade
- Excel as Calculator
- Excel Formula Not Working (Updating)
- Excel Table Styles & Formats
- Excel vs Google Sheets
- External Links in Excel
- Excel Alternate Row Color
- Excel Worksheet Tab
- Extract Number from String Excel
- Evaluate Formula in Excel
- Find Duplicates in Excel
- Finding Links in Excel
- Filter Shortcut in Excel
- Formatting in Excel
- Format Numbers to Millions & Thousands in Excel
- Format Phone Numbers in Excel
- Formula Errors in Excel
- Fractions in Excel
- Frequency Distribution in Excel
- Group in Excel
- Group Worksheets in Excel
- Group Columns in Excel
- Hide Formula in Excel
- Hiding a Column in Excel
- Highlight Every Other Row in Excel
- Highlight Duplicates in Excel
- How to Create a Formula in Excel?
- How to Create an Excel Spreadsheet?
- How to Add Text in Excel Formula?
- How to Create Dashboard in Excel?
- How to Copy Sheet in Excel?
- How to Delete Pivot Table?
- How to Calculate Percentage Increase in Excel?
- How to Multiply in Excel Formula?
- How to Unhide Columns in Excel?
- Insert Date in Excel
- Insert Calendar in Excel
- Import Data into Excel
- Insert Comment in Excel
- Insert Hyperlinks in Excel
- Insert Multiple Rows in Excel
- Insert Row Shortcut in Excel
- Insert New Worksheet in Excel
- Insert (Embed) an Object in Excel
- Insert Image in Excel Cell
- Insert Page Break in Excel
- Line Breaks in Excel
- Linear Interpolation in Excel
- Leading Zeros in Excel
- Last Day of the Month in Excel
- Logical Operators in Excel
- Lookup Table in Excel
- Mortgage Calculator in Excel
- Moving Average in Excel
- Not Equal to in Excel
- Numbering in Excel
- Name Manager in Excel
- Page Numbers in Excel
- Page Break in Excel
- Personal Budget Template in Excel
- Project Management Template in Excel
- Percentage Difference in Excel (Increase / Decrease)
- Pivot Table Calculated Field & Formula
- Pivot Table Sort
- Pivot Table From Multiple Sheets
- Print Comments in Excel
- Print Excel Gridlines
- Print in Excel
- Print Preview in Excel
- Print Area in Excel
- Print Titles in Excel
- Print Labels From Excel
- Project Timeline in Excel
- Protect Sheet in Excel
- Ratio in Excel Formula
- Random Numbers in Excel
- Randomize List in Excel
- Refresh Pivot Table in Excel
- Relative References in Excel
- Remove Blank Rows in Excel
- Remove Duplicates in Excel
- Remove Duplicates from Excel Column
- Remove Hyperlinks in Excel
- Remove Space in Excel
- Remove Leading Spaces in Excel
- Remove Watermark in Excel
- Row Limit in Excel
- Rows and Columns in Excel
- Rows to Columns in Excel
- Row Header in Excel
- Search in Excel
- Search For Text in Excel
- Share an Excel Workbook
- Shortcut to Merge Cells in Excel
- Show Formula in Excel
- Split Cells in Excel
- Separate Text in Excel
- Strikethrough in Excel
- Strikethrough Text in Excel
- Sum by Color in Excel
- Subscript in Excel
- Superscript in Excel
- Themes in Excel
- Timesheet Calculator in Excel
- Top 20 Keyboard Shortcuts in Excel
- Unmerge Cells in Excel
- Uppercase in Excel
- Word Count in Excel
- Word Cloud in Excel
- Watermark in Excel
- Weighted Average in Excel
- Wildcard in Excel
- Financial Functions in Excel (17+)
- Logical Functions in Excel (15+)
- TEXT Functions in Excel (29+)
- Lookup Reference in Excel (44+)
- Maths Functions in Excel (52+)
- Date and Time Function in Excel (22+)
- Statistical Function in Excel (50+)
- Information Functions in Excel (5+)
- Excel Charts (48+)
- Excel Tools (98+)
- VBA (162+)
- Power Bi (35+)
Linear interpolation means forecasting or guessing the upcoming next value of any certain variable given on the current data, in excel we create a straight line which connects two values and we estimate the future value through it, in excel we use forecast function and a lookup function to do a linear interpolation.
Excel Linear Interpolation
Interpolation is a mathematical or statistical tool which is used to predict the values between 2 points on a curve or line. This tool is not only used in statistics but also used in many other fields like business, science, etc. wherever there is an opportunity to predict values between two data points.
How to do Linear Interpolation in Excel?
Let us see how can we do interpolation and linear interpolation in excel by looking at below examples.
Example #1 – Performing Interpolation to know the temperature of weather during different time zones
First, take down the temperature figures of Bangalore region for each hour and data would be as follows: –
The data shows that we have got the temperature details of Bangalore region for some date. On time column we have the time zones for the entire day and hour column we mentioned the hours’ number from the starting of the day like 12:00 AM would be 0 hours, 1:00 AM would be 1 hour and so on.
Now we are going to perform interpolation for the data in order to pull out the temperature value for the required time zone which may be any time not only the exact hour.
In order to perform interpolation, we have to use a few formulas in Excel like FORECAST, OFFSET, MATCH. Let us see in brief of these formulas before we go ahead.
FORECAST() – This Forecast excel function calculates or predicts the future value based on existing values along with a linear trend.
- X – This is the value for which we want to predict.
- Known_ys – This is the dependent values from the data and a mandatory field to be filled
- Known_xs – This is the independent values from the data and a mandatory field to be filled.
MATCH() – This Match excel function will return the relative position of a lookup value in a row, column or table that matches the specified value in a specified order.
- Lookup_value – This is the value that needs to be matched from the lookup_array
- Lookup_array – This is the range for searching
[match_type] – This can be 1,0,-1. Default would be 1. For 1 – Match will find the largest value which is less than or equal to look_up value and value should be in ascending order. For 0 – Match finds the first value exactly equal to lookup_value and no need to be sorted. For -1 – Match will find the smallest value which is greater than or equal to the look_up value and should be sorted in descending order.
OFFSET() – This Offset function will return a cell or range of cells which are specified number of rows and columns. The cell or range of cells will depend on the height and width in rows and columns we specify.
- Reference – This is the starting point from where the count of rows and columns will be done.
- Rows – No.of rows to offset below the starting reference cell.
- Columns – No.of columns to offset right from the starting reference cell.
- [height] – The height in rows from the returned reference. This is optional.
- [width] – The width in columns from the returned reference. This is optional.
As we have seen the formulas in brief that we are going to use to perform the interpolation. Now let us perform the interpolation as follows:
Type the formula in a cell that we need to see the temperature for different time zone. This tells that we have to select the cell that needs to be forecasted and offset & match function is used to select the known_ys and known_xs.
FORECAST($F$5 – Select the cell that has the time zone to be forecasted.
OFFSET($C$3:$C$26,MATCH($F$5,$B$3:$B$26,1)-1,0,2) – This is used to select the known_ys as a reference is taken temp column because these are the dependent values. The match function is used to generate the position of the value that we need to forecast and calculate the number of rows. Columns should be 0 because we want the dependent value on the same column which selected and height is 2 as we need to perform the forecast based on last 2 values.
OFFSET($B$3:$B$26,MATCH($F$5,$B$3:$B$26,1)-1,0,2) – This is used to select the known_xs as a reference is taken hour column because these are independent values and rest is the same as we had done for row count.
Now give some time zone in the cell that we had considered to forecast. Here the value entered is 19.5 which is 7:30 PM and we will get the temperature of 30 which is forecasted from the temperature values that are given an hourly basis.
Similarly, we can see temp figures for different time zone from this formula.
Example #2 – Performing Linear Interpolation to know the sales of an organization in 2018
Let’s assume that we got the sales details for an organization in 2018 as below. We have data in terms of days and their sales in cumulative. We got sales of 7844 units in first 15 days of the year, 16094 units in 50 days of the year and so on.
We can use the same formula that we used in interpolation to forecast sales value for different days which was not mentioned in the data that we consider. Here the sales are in a straight line(linear) as we had taken cumulatively.
If we want to see the number of sales that we had achieved in 215 days then we can get the forecasted number of sales for 215 days as below by considering the given sales data.
Similarly, we can find out the number of sales in that year by forecasting between the points that are given.
Things to Remember About “Linear Interpolation” in Excel
- Linear Interpolation in Excel is the least accurate method but it is fast and accurate if the table values are closely spaced.
- This also can be used in estimating values for a geographical data point, rainfall, noise levels, etc.
- It is very easy to use and not very accurate for non-linear functions.
- Apart from Excel Linear interpolation we also have different types of methods like Polynomial Interpolation, Spline Interpolation, etc.
You can download this Linear Interpolation Excel Template here – Linear Interpolation Excel Template
This has been a guide to Linear Interpolation in Excel. Here we will see how to perform interpolation and linear interpolation in excel along with some examples and downloadable excel template. You may learn more about excel from the following articles –