The functions of this Add-in are very simple to use and they have context help, through a chm file. Next, double-click on the trend line. Mutakhirkan ke Microsoft Edge untuk memanfaatkan fitur, pembaruan keamanan, dan dukungan teknis terkini. I have a model where a certain boost leads to a certain rate. LINEAR formula to the cell where you want to add the interpolated value. xll. a polynomial that assumes certain values at each given position. To extrapolate data by formula, we need to use two points of the linear chart that we plotted before. The different types of this concept include linear interpolation among others which are discussed below. The linear interpolation draws straight lines from each point to the next. Now let's do the linear interpolation. Adding the module automatically opens a new code window. Method 2: Interpolate in Excel Graph Using Trendline. This produces the value 36. The first step to performing linear interpolation with this method is to create a chart with all of your data to optimize your organization and ensure accuracy in your calculation. This tip explains how to use the function. Here, we create a. Video transcript. Rem Linear Interpolation from a x and y ordered data, for a value: Rem TODO: make it work with unordered Data. Disappointing. forcast wants a range or an array and not numbers which is what you are passing with indexys:indexys1 You are passing the values of the index, so it would return something like 104:34 and not a range or an array. <colgroup><col width. Jan 27, 2021 at 18:02. First, you need the data graph so Excel can fit a line to the data and g. I am given a value n and a value r0 and need to get a value from the given table. Then. Save as . This is the lower value for interpolation. This results in a continuous curve, with a discontinuous derivative (in. 1. These boosts are defined as 10%, 20%, 30% until 100%. The X and Y data are in columns A and B. As we can see from the image above, point P is situated in the rectangle defined by the values 22. Scene 8: Now copy the formula to the remaining cells in the column, let's see how they look. Steps: First of all, let’s assume Depth of Flow = X and Discharge = Y. linear interpolation of sparse data I have several lists of data, each sampled at different varying intervals. Syntax. Interpolation with FORECAST. Do Interpolation of Non-Linear 2D Data in Excel. . Piecewise linear interpolation Figure 3. S. ^> 1. Most common practise is to linearly interpolate. Method-5: Using the Combination of INDIRECT, ADDRESS, and MATCH Functions. richthorpe, this post is unlikely to be very useful for your purposes, but you may get some interest from it. Using Formula for Data Extrapolation in Excel. All you have to do is to type in (or paste in) the concentrations of the standard solutions and their instrument readings (e. Another technique uses piecewise linear interpolation. The linear interpolation is commonly used in computer graphics. For example, Code: A --B-- 1 x y 2 1 1. Enter the following formulas into Cells A2-F2, and then copy them down the page. - So, we've seen two ways to calculate in-betweens: linear interpolation and Bézier curves. Rows. Follow these steps to learn how to do it. Cells. Using Excel’s Fill Command feature, you may automatically fill a set of cells with similar content or with a collection of numbers or letters. Utilizing TREND Function for Exponential Interpolation in Excel. This tutorial explains how to use linear interpolation to find some unknown. Examples of seven- and five-point scale items. Cell A2: Copy your weight data into the first column. Therefore, you cannot ask the interpolation of a single number - rather, you need multiple numbers to determine your line, and from there you can ask what value you would. Public Function InterpolateXY (ByRef X As Range, ByRef Y As Range, ByRef XRange As Range, ByRef YRange As Range, ByRef ValueTable As Range) As Double. > spreadsheets like Quattro Pro do). 4. Here, we have a dataset containing the values of X and Y, where Y=F(X). , X1 & Y1. Add a comment. The above formula is the Linear Interpolation Formula. If you'd like you can set up a little interpolation calculator like this so you can plug in the missing date and get the. LINEAR($I$1,INDEX(F2:H2,MATCH($I$1,$F$1:$H$1,1)):INDEX(F2:H2,MATCH($I$1,$F$1:$H$1,1)+1),INDEX($F$1:$H$1,MATCH($I$1,$F$1:$H$1,1)):INDEX($F$1:$H$1,MATCH($I$1,$F$1:$H$1,1)+1))One of the basic approaches of data interpolation in Microsoft Excel is to apply the mathematical formula of linear interpolation. If you are stuck with Excel, here's how you do it. There are a few different types of interpolation techniques. The symbols N (P) and N (Py) will denote the norms of P and Py. To use an interpolation function, click on the fx button on the formula bar, select the XonGrid 4 interpolation category, select a function then enter your data. Since we want to evaluate the velocity at t = 16 and use linear spline interpolation, we need to choose the two data points closest to t = 16 that also bracket t = 16 to evaluate it. The correct formula to use is of the form: =IFERROR. Use Tab to toggle from the function arguments fields. You can use interpolation to fill-in missing data, smooth existing data, make predictions, and more. Now, we will show you how you can interpolate polynomially the value of Y from the given value of X by drawing a scatter chart in Excel. By mike0123m in forum Excel Formulas & Functions Replies: 8 Last Post: 09-28-2007, 02:09 PM. This simple and powerful Excel linear interpolation function was developed using VBA and can be called from within your excel workbook. Post a small Excel sheet (not a picture) showing realistic & representative sample data WITHOUT confidential information (10-20 rows, not thousands. One frequent problem is that the SOC vs V OC curve is frequently not a one-to-one function. Steps: First of all, let’s assume Depth of Flow = X and Discharge = Y. In this method, we will use a mathematical formula to calculate logarithmic interpolation in Excel. Function LinInterpolation(DataX As Range, DataY As Range, point As Range) As Variant: If point. x = Known value. The expression that helps calculate extrapolation is as follows: Y (x) = Y (1)+ (x- x (1)/x (2)-x (1)) * (Y (2) – Y (1)) The formula for linear extrapolation can be divided into the following steps: First, one must analyze the data to. I took 2 screenshots. I use the following equations to find the two values either side of 50:Go into your code and set a break point (F9 key). In this video, we'll show you how to interpolate a value using the Linear Trendline. I want to merge them into an average set of readings, but since they are each missing data and have readings at different times, I need to first fill-in all missing data using some (linear) interpolation. LINEAR function predicts a value based on existing values along a linear trend. 1. example. Dados dos valores conocidos (x 1 , y 1 ) y (x 2 , y 2 ), podemos estimar el valor de y para algún punto x usando la siguiente fórmula: y = y 1 + (xx 1 ) (y 2 -y 1 ) / (x 2 -x 1. Here, we create a straight line that connects two values and estimates the future value. 31 6M 0. Nonlinear Interpolation With Excel To Construct U. 66, 5+3. To do this, right-click on any data point and select ‘Add Trendline. x: It is the predicted value. The formula to calculate linear interpolation is: Linear Interpolation (y) = y1+(x−x1) (y2 −y1) (x2 −x1) y 1 + ( x − x 1) ( y 2 − y 1) ( x 2 − x 1) where, x1 x 1 and y1 y 1 are the first coordinates. To do this, click on Chart Design > Select Data. For detailed instructions on how to do nonlinear interpolation within an Excel. If developer tab is not available in your Excel, you need to go to Files > Options, click Customize Ribbon and check the box. The x1 value is the value in the table that is just below our desired x-value of 230, and the x2. Given a function with a set of points one can form a function such that for (that is, that interpolates at these points). In my career, about 90% of this work has involved batteries (always nonlinear) or sensors (frequently nonlinear). Multivariate interpolation. The formula for bilinear interpolation is: This is a complicated formula, but it can be broken down. From this we get the simple linear interpolation formula x = fx2 +(1¡f)x1 (lin) : (3) Logarithmic scale The situation is a little less straightforward if the axis is not on a linear scale but rather on a logarithmic scale. The GROWTH function is a special function to interpolate non-linear data. Question: Find the value of y at x = 4 given some set of values (2, 4), (6, 7)? Solution: Given the known values are, x = 4 ; x 1 = 2 ; x 2 = 6 ; y 1 = 4 ; y 2 = 7. Outside-range values are easily diagnosed because it throws errors in the intermediate table when you go off the edges. Finding the x and y values. In linear interpolation, you might get a new data point from two known data points. Since bilinear interpolation has a complicated formula, we will use the INDEX function and the MATCH function to find the x1, x2, y1, y2, and the Q values to fill in the values table. Vector x contains the sample points, and v contains the corresponding values, v ( x ). ★ Want to automate Excel? Check out our training academy ★ Download the example file★ Here, (x1, y1) = The First coordinate of the interpolation process. Remarks. I’ve looked at the thirty-three-and-a-third percentile expecting it to usually be a simple linear interpolation of the 33rd and 34th percentiles (ie. The Y- coordinates even start descending and ascending multiple times. , C1), type the following formula: = (B5 - B3) / (A5 - A3) * (A4 - A3) + B3. You will use the FORECAST function when you need a function to forecast your data without making charts and graphs. Now, whenever the k value you specify is not a multiple of 1/(n-1), PERCENTILE. Next, type in the formula =A2^2 in cell B2. The FORECAST. To find the y and z values, we’ll just need to modify our formula to use the y-coordinates and z-coordinates of our points. Introduces a method to calculate interpolation step value in Excel. S. A logarithmic scale simply means that valuesExcel's new LAMBDA functionality (Office 365/Excel 2021, beta version, insiders only) - the capability to define your own function within Excel directly - makes that several User Defined Function. INTERPXY is a versatile 2D interpolation function based on splines. Define names for the KnownX and KnownY ranges (InsertNameDefine. To be able to determine what the rate will be at, for. Interpolation is the process of estimating an unknown. 3) A lookup function (usually using approximate match option) to return slope and intercept at the specified date from the monthly table. Count If nR < 2 Then Exit Function If x < rX (1) Then ' x < xmin, extrapolate l1. To perform linear regression in Excel, you'll need to use the "LINEST" function, which calculates the slope and intercept of the line that best fits the data. You want to obtain the value (by linear interpolation) for height of 7 and distance of 20. A few methods of Interpolation are the following: Linear Interpolation: A straightforward approach for predicting the value of a function at a position between two known data points by determining the equation of the line that connects these two points. 15(entered in cell I1) =FORECAST. Interpolation is the process of estimating an unknown value of a function between two known values. Jon Peltier says. Its simplest formula is given below: y = y1 + (x−x1)(y2−y1) x2−x1. But in excel I don't even know where to start, maybe. , see screenshot: Step 3: In Fill Blank Cells dialog box, check the Linear values option and From left to right option. If you have some idea of the shapes of the underlying yield curves, you can work. The yellow shaded cell, A2, holds the known X value, and a formula in cell B2 holds the calculated Y value. 76 and at the higher discount rate rH of 9%, PVH is $934. Methods. In the Format Trendline pane, select the Linear option, and ensure that Display Equation on Chart is checked. Interpolation. Next, we will select the range of our 4×4 grid of neighboring data points. And here it is the definitive 1 Excel to perform Linear Interpolation (single entry tables) and Bilinear Interpolation. Where: y - the dependent variable you are trying to predict. Define a vector of x values for which you want to interpolate the curve. Guide September 2, 2023 How to Interpolate in Excel Interpolation is a powerful tool that allows users to predict data between known values. First, create a scatter chart. The main factor about spline is that it combines. 48 4Y 0. The solution can be either in one formula that "looks" at target achievement and returns a corresponding bonus award amount (ideally), or via an intermediary data table (e. The interpolation problem is: given a set of pairs of values (x_i, y_i) for i in (0,N+1), find a function p(x) within a particular class (usually polynomials) such that p(x_i) = y_i. (x2,y2) = Second point of the interpolation process. It allows you to do linear or spline interpolation for a general curve in n-dimensions. Linear Interpolation - Key takeaways. . In this case, F7=7 and E7=3 because B already included the integer 7 and has a matching value in column A. Count If nR < 2 Then Exit Function If x < rX (1) Then ' x < xmin, extrapolate l1. The future value is a y-value for a given x-value. Then go into the spreadsheet and get that cell to calculate. Excel Linear #Interpolation using the #FORECAST built-in function#excel #linearinterpolation #math #finance #accounting #science #engineeringWhen you are doing a linear interpolation, you are approximating the function between points as a line. Next, x refers to the known value, while y is the unknown value. Once we click OK, Excel automatically fills in the missing values by adding 3 to the each. Count If nR < 2 Then Exit Function If x < rX (1) Then ' x < xmin, extrapolate l1 = 1: l2 = 2: GoTo. known_x’s – a range of x-coordinates. Using Excel GROWTH Function for Nonlinear Interpolation. Linear interpolation is often not accurate for non-linear data. In order to acomplish this I need to find two values in B, the closest to 50 without going over and the closest without going under e. 2003) and replace NewX with the x-value to interpolate. Step 3: Finally, the interpolated point will be. "Click on ‘Insert’ and expand the dropdown for ‘Scatter Chart’ and select the ‘Scatter’ thumbnail (first one) Now a scatter plot will appear, and we will draw the regression line. Line 5 gives us the higher value for interpolation: 5: MINIFS(B:B,B:B,">" &VLOOKUP(D5, A:B, 2, TRUE)) It searches column B for the smallest value (using the MINIFS function) but applies the condition that the smallest value should be larger than the value found by the lookup in line 4. Excel is an excellent tool for interpolation, as ultimately, it is a big visual calculator. Piecewise linear interpolation. Follow these steps: Start with the worksheet shown in Figure 63. My problem is this: I have some data from a geophysics survey in a simple xlsx sheet (Excel 2010). Step 2: Fill the formula with the desired values. After that, go to Add Chart Element > Trendline > Linear. So the formula is y = y1 + (x-x1) (y2-y1)/x2-x1 . Right click on your chart > Add trendline > Under Trendline options, select one by one and see which is the best fit > Go down and check Display Equation on Chart. It keeps your code clean, short, tidy and. Solution. 4 when we asked for the 80 th percentile earlier?FORECAST. In particular, take interpolation nodes a x 0 < x 1 < < x n 1 < x n b and produce the interpolation polynomial P n(x) that interpolates f(x) at the given node points. Linear Interpolation Formula is the process of finding a value between two points on a line or curve. I assume that you want linear interpolation between 2 x values. This means that the V OC does not map to a unique. If you can express your problem as a system of linear equations, excel has a few matrix formulas. To start, place at least two columns of data in an empty Excel worksheet. Then. Sincerely yours, Vijay A. Alternatively,. [Edit]: You will need to sort your arrays on the x axis as @ImportanceOfBeingErnest adds. asked Nov 21, 2022 at 20:57. Lompati ke konten utama. n) lies on the line (AC) between the two known rates. 2) Calculate slope and intercept for each pair of points (for each firm) as part of the lookup table. 000 3 2 2. Interpolation is a mathematical or. Now I'm finding a way to interpolate it into a power function y = a + b x c. use FORECAST () to get a linear interpolation between 19 and 22. How to Use the Interpolation Calculator? The procedure to use the interpolation calculator is as follows: Step 1: Enter the first and second coordinate, and the point to perform the interpolation in the respective input field. The function to be interpolated is known at given points and the interpolation problem consists. If you're stuck working in Excel. S. Fig 1: Interpolation example - sample NA vector in column C (top panel), and interpolated value, with A1 style in FormulaBar and R1C1 returned by the Excel. With the VBA editor open, insert a module into the workbook by right-clicking on the workbook in the project window and selecting Insert>Module. Its general formula is. Hello, I am trying to get an excel formula that will do linear interpolation for me. You can determine the other coordinate for a new value for y or x by inputting that value. This the ‘Bezier’ function. – Scott Craner. Example of bilinear interpolation on the unit square with the z values 0, 1, 1 and 0. First, organize your data into two columns with known values. I would like to write an Excel function that linearly interpolates between data points. A (x 1 ,y 1) and C (x 2 ,y 2) are the two points around B. There is a very common. #10. The objective is to replace #N/A errors in a data vector (figure 1 top panel) with formulas returning the interpolated value (figure 1 bottom panel). Linear interpolation is the process of finding intermediate values in data within the known range. If our data set follows a linear pattern, we can use this method to. The mathematical equation for this case is as follows: y= y 1 + (x-x 1 )⨯ (y 2 -y 1 )/ (x 2 -x 1) We need the value of y corresponding to x, which makes point B (x,y). It is easy to perform interpolation of non-linear 2D data in Excel. It uses exponential regression to predict a value. And our initial data set would look like this: Firstly, we can simply input the basic mathematical formula of linear interpolation in Excel to get the result. However, F8=8, which is an intermediate value not contained in column B. Treasury Bond Yield Curve R Corner - Creating Historical Portfolios Recommended Application: Instapaper Number Puzzle October Number Puzzle Solutions NONLINEAR INTERPOLATION WITH EXCEL TO CONSTRUCT U. For the 32 bits version of Excel select XonGrid. Furthermore, scroll down and check the option ‘ Display Equation on chart ’. Lagrange Interpolation Formula finds a polynomial called Lagrange Polynomial that takes on certain values at an arbitrary point. Download the Excel file for details. So why did we get 183. We called out the WorksheetFunction Index and Match. e. Another basic approach for interpolation is the Nearest Neighbors Interpolation in which you estimate each value by using the nearest neighbor. The FORECAST formula not only finds an incorrect intermediate value, but also, as the example shows, inserts an already known value incorrectly. known_x’s – a range of x. Friday, March 1, 2013 at 8:04 pm. Here, we have a dataset containing the values of X and Y, where Y=F(X). In Excel 2016, the FORECAST function. Here, the constant is usually set as 1 to derive the adjacent values. If the points in the data set change by a large amount, linear interpolation may not give a good estimate. This video demonstrates how to use ExceLab Add-in INTERPXYZ() function to interpolate scattered (x,y,z) points onto a uniform grid and plot the data with Exc. One of the basic variants of interpolation is the linear interpolation technique which is commonly used by analysts in the field of mathematics, finance and computer programming. 56 5Y 0. 1. 64. Do Interpolation of Non-Linear 2D Data in Excel. known_y’s: An array of the dependent variable or range of data. Scene 7: Edit the formula and change the interpolation algorithm to three or linear interpolation then click OK. Get it Now. Figure 1. To avoid this, the interpolation has to happen between two points only. The interpolation formula uses interpolation, which is the process involving finding a value between two points on the curve of a function. e. Linear interpolation in Excel means forecasting or guessing the next value of any certain variable given on the current data. You need two variables to read off the compressed liquid or superheated vapor tables. Linear Interpolation in Excel: Step-by-Step Example. HOW TO ATTACH YOUR SAMPLE WORKBOOK: Unregistered, Fast answers need clear examples. Solution: The procedure you use to solve this problem is essentially an extension of the method used for the single control parameter table. In Excel, we can use linear interpolation with functions and formulas. We’ll need to give the function a name – I’ll call it “LinInterp”. See the yellow-highlighted cells in my "corrected" sheet in the attached Excel file. The nth-order polynomial is 𝑛( )= 0+ 1( − 0)+⋯+ 𝑛( − 0)( − 1)…( − 𝑛−1) As was done previously with the linear and quadratic interpolations, datainterpolation avec Excel sans fonctions en utilisant des simples formules ; vous cherchez une méthode simple et rapide pour faire de l'interpolation, dans ce. If you are not committed to Excel, you could try Quattro Pro that does have a built in linear interpolation (@LINTERP()) function. I'm trying to construct a workbook with the ability to interpolate between 2 values. 32, 10 as the series. 5 in this case. First, the. The steps to calculate the Interpolation in excel using the FORECAST formula are, Select cell C2, and enter the formula =FORECAST. as in following example. (x2,y2) = Second point of the interpolation process. Generating piecewise linear approximations for non-linear curves is a very common task. This custom 2-dimensional linear interpolation function, =Linterp () *, accepts 3 arguments: =Linterp (KnownYs, KnownXs, NewX) The order of the arguments is the same as in the Excel worksheet function =TREND (). LINEAR Result: GROWTH Result: FORECAST Inner-linear interpolation (manual) FORECAST Inner-linear interpolation (automatic) Author: Excel Off The Grid Last modified by: Mark Proctor Created Date: 4/8/2020 7:55:27 PM Other titles:Formula to Extrapolate in Excel. You can use Excel to project values that are based on existing data or to automatically generate values based on linear or growth trend calculations. I don't know about other spreadsheets, but Microsoft just doesn't seem to want to put a linear interpolation function into Excel. The formula now is =FORECAST. 2. Step 1: Add the FORECAST. Google groups is your friend. It is easy to perform interpolation of non-linear 2D data in Excel. I want to do this using formulae if possible. Also, it calculates (or approximates) the. Step 2: Perform multiple linear regression. If you extrapolate based on x much, much less than x_list[0] or x much, much greater than x_list[-1] , your return result could be outside of the range of. Select the chart to see two new tabs in the ribbon, “Design” and “Format. The formula for linear interpolation is y = y 1 + ( x − x 1) ( y 2 − y 1) ( x 2 − x 1) Linear interpolation can also be used to find the median, 1 st quartile and 3 rd quartile. linear interpolation, which assumes developmentproportional to time over the period. a. Options for interpolation with Excel; Interpolation using simple mathematics; Interpolation using the FORECAST function; Interpolation when perfectly linear; Interpolation when approximately linear; Interpolation when the data is not. User case. the difference between the tercile and the 33%-ile should be a. These values need to be in increasing order. Linear interpolation is used to find an unknown value of a function between any two known points. There are many ways to perform linear interpolation (or extrapolation) in Excel. And (x2, y2) refers to the second point of the interpolation process. This the ‘Bezier’ function. The order the data is coming is variable and the difference of the timestamps is also variable. x - the independent variable you are using to predict y. 🔎 Code Explanation. Write down the numbers that you are going to put. Tabular Data Extrapolate in. You only need to revise the input unknown X and input know. When the data is not linear, the first thing we have to do is to find out the function's behavior. Its syntax is: FORECAST (NewX, known_y_pair, known_x_pair). In other words, interpolate the column E values corresponding to the column F values. This is the lower value for interpolation. However, sometimes actuaries are more comfortable with the assumption that more will be reported or paid. TREASURY BOND YIELD CURVE by Andrew Chan Excel offers. Wenn zwei bekannte Werte (x 1 , y 1 ) und (x 2 , y 2 ) gegeben sind, können wir den y-Wert für einen Punkt x unter Verwendung der folgenden Formel schätzen: y = y 1 + (x - x 1 ) (y 2 - y 1 ) /. Compulsory Parameters: x: A numeric x-value for which you want to forecast a new y-value. The formula for bilinear interpolation is: This is a complicated formula, but it can be broken down. Linear interpolation - example. Function Linterp2 (rX As Range, rY As Range, x As Double) As Double ' linear interpolator / extrapolator ' R is a two-column range containing known x, known y Dim lR As Long, l1 As Long, l2 As Long Dim nR As Long 'If x = 1. Excel charts are a convenient way to fit a curve to experimental data. For example, i have 5, , ,10. ____How to use:____ This Add-on does not have special UI on the sidebar. I was given a sheet that shows what it should look like when finished, but I just don't know how to get those values: It looks like the 2018 value should be 370,779, the 2019 value should be 458,258, and the 2020 value should be 566,375 (fod D5, E5, F5) - Your help is so appreciated by the way! I'm struggling on this one!You can choose multiple other non-linear interpolations too (quadratic, cubic and so on). known_x's is the range of independent x-values (historical data). This browser is no longer supported. Note: If you have a current version of Microsoft 365, then you can input the formula in the top-left. There are various ways to interpolate: spline, polynomial, linear and so on. Things to. The interpolation formula is explained below along with a few solved examples in the following section. power head plot power head example Noise source plot S-parameter angles S-parameter example Noise source example Enter your data step-by-step plot closeupGenerating piecewise linear approximations for non-linear curves is a very common task. Step 3: Use the Interpolate Function. Use INTERPXY to interpolate from a set of (x,y) data points at an arbitrary point. Here are the steps you can take: 1. Note: Starting with Excel 2016, the FORECAST function was. To use linear interpolation in Excel, you will need to determine the two known values, then use the slope-intercept form of the equation of a line to calculate the value that lies between them. To make the above LinearInterpolation function available in excel like other functions, you need to carry out the following steps:. Use the following Formula in the cell I2 to interpolate for the value 0. I would do linear interpolation between the closest pair on the x-axis. Because, as per the input data, the values corresponding to x = 1 is 1. 9. Volatile Dim hi As Long Dim lo As Long Count = XRates. I wrote a function in VBA to interpolate, giving the option of Arithmetic or geometric interpolation (but assuming arithmetic as the default), and using the standard vlookup function. The N-1 Basis method is used by Excel’s legacy QUARTILE function and by the QUARTILE. If you plot it with a logarithmic y axis, it's linear (perfectly linear; yours surely isn't if. ----- Pairwise linear interpolation is demonstrated in B17 and B18. Linear extrapolation can help us estimate values that are either higher or lower than the values in the data set. Define a set of y values. 47. One relies on creating a curve of best fit taking into account the entire data set. [SOLVED] 3d interpolation of a table. Update the input column from D to E. Example: Linear Interpolation in Excel: Consider the following dataset in Excel: Linear Interpolation in Excel. Type. In Microsoft Excel, the most commonly used function for non-linear interpolation is =GROWTH (known Ys,know Xs,new Xs,constant). 5 as indicated. Using Linear Regression for Extrapolation in Excel. The =INDEX and =MATCH pick out the relevant two points on known_y's and known_x's. The TREND Function is an Excel Statistical function that estimates an unknown value based on linear regression.