Excel does not provide a function for linear interpolations. If your data table has a low granularity (you have only units, not sub – units), and you need precise results, you have to create your own linear interpolation formula. You will find in this article an excel formula, and a User Defined Function (UDF) for Linear Interpolation in Excel.
Introduction to Interpolate in Excel Interpolation is a method that is used to estimate or find out a value between two known values on a line or curve. The linear interpolation equation above can be implemented directly in Microsoft Excel provided the tabulated values are monotonic in x, that is the x-values are sorted and no two are equal. The online Microwave Encyclopedia has the full 6 line implementation.
Granularity refers to the level of detail of the data; a high level of detail means a low level of granularity, a low level of detail means a high level of granularity.
Basically, the Excel formula is simple: =_X1+(_X2 – _X1)*(Y – _Y1)/(_Y2 – _Y1)
This formula can be written in a more familiar style, the relationship between the known points (X1,Y1) and (X2,Y2) and unknown location of X, Y is:
For the below data table, the problem is that finding the _X1, _X2 and _Y1, _Y2 values requires 4 different formulas, written in 4 defined names. I will not dive into details, but you can download the sample workbook, to see how it works, you will find a lot of details and explanations for each part of the formula.
[caption align='aligncenter' width='214'] Data Table Microphone macbook pro not working.
Download sample Workbook:Interpolation.xlsm
The UDF I developed for Linear Interpolations, makes life easier 🙂
Here is the VBA code:
The function is flexible, you can use it for both vertical or horizontal data tables, changing the last argument to TRUE or FALSE. All arguments are explained inside the code, and in the sample workbook, where you will find examples for both situations.
For the data table presented above, the UDF will look like this in a worksheet formula:
=Interpolate(Y, DataTable, 2, TRUE)
Note that the 4th argument for data table layout is optional, and by default, the value is TRUE (for Vertical data tables), you can omit this argument for vertical tables. If your data table is presented in an horizontal layout, the 4th argument must not be omitted, and the value should be FALSE:
=Interpolate(Y, DataTable, 2, False)
Excel Interpolate Table
If this function makes you happy, let me know 🙂