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). Let's say we have two data series x_i and y_i such that for all i in [[1,N]], y_i = f(x_i) where f is a non-linear function. The LINEST function calculates the statistics for a line by using the "least squares" method to calculate a straight line that best fits your data, and then returns an array that. e. I would do linear interpolation between the closest pair on the x-axis. 78. Step 2: Input Required Data into Cubic Spline Model. > Excel doesn't have a built in linear interpolation function (some other. In our example of paddy growth, the first set of values is (4,2). 1W 0. The second set of values is (8,4). Beispiel: Lineare Interpolation in Excel. Here the interpolated value of 2. The formula is y = y1 + ( (x - x1) / (x2 - x1)) * (y2 - y1), where x is the known value, y is the unknown value, x1 and y1 are the coordinates that are below the known x value, and x2 and y2 are the coordinates that are above the x value. Here's how our example points fit into the formula: y = 152 + (170-152) x [ (13-12) / (14-12)] 5. The interpolation formula uses interpolation, which is the process involving finding a value between two points on the curve of a function. An extrapolated expression is an expression used to estimate the value of a dependent variable for an independent variable that is said to be certainly outside the scope of a particular known dataset and to compute a linear search. 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. Add input cells for height and span in J1 and J2 respectively. 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. If our data set follows a linear pattern, we can use this method to. 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 (). It is an nth-degree polynomial expression of the function f(x). It fits a straight line (using the method of least squares) to the array's known_y's and known_x's. It is easy to perform interpolation of non-linear 2D data in Excel. And (x2, y2) refers to the second point of the interpolation process. Let's start with the simpler version, linear interpolation. Press Enter. A function to interpolate along Excel’s smooth line fit. Introduces a method to calculate interpolation step value in Excel. To find the interpolated value for a particular x, Mathcad finds the two points between which the value falls and returns the corresponding y alue on the straight line between the two points . This sounds complicated, but it basically means Excel will settle on a value between the two. Given two known values (x1, y1) and (x2, y2), we can estimate the y-value for some point x by using the following formula: y = y1 + (x-x1) (y2-y1)/ (x2-x1) This tutorial explains how to use linear interpolation to find some unknown y-value based. 3. Rem Linear Interpolation from a x and y ordered data, for a value: Rem TODO: make it work with unordered Data. However, F8=8, which is an intermediate value not contained in column B. (x2,y2) = Second point of the interpolation process. Hello, I am trying to get an excel formula that will do linear interpolation for me. Interpolate in Excel Graph: 6 Ways. If you have an old release of Interpolation. Call the linterp function to perform linear interpolation between the data points. It is a simple yet effective technique that calculates the value of a variable (typically y) at a specific point (x) by considering the values and positions of two. The following Microsoft Excel formula performs linear interpolation by calculating the interpolation. 3 is the value just before 3. Interpolación lineal en Excel: ejemplo paso a paso. Given (x1, y1) and (x2, y2), we can estimate the y-value for a given x-coordinate. Interpolate Excel Formula. which is 20000 + 0. So the formula is y = y1 + (x-x1) (y2-y1)/x2-x1 . Count > 1 Then: LinInterpolation = "Can only interpolate one value" Exit Function: End If: Dim value As Double: value =. Below is a close-up of an interpolated data point. In Excel 2016, the FORECAST function. A (x 1 ,y 1) and C (x 2 ,y 2) are the two points around B. Introduction to the Excel Template. 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. A function to linearly interpolate and extrapolate. Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support. known_y’s: An array of the dependent variable or range of data. Description. Linear Interpolation – This method is quick and easy to use, also known as Lerp. 4) Solve y=mx+b to interpolate. Linear Method: This method calculates the data by applying an exclusive linear polynomial between every pair of data points for each curve or for a set of three curves for surfaces. The resulting scatter chart will plot the result of our cubic spline interpolation. This means that the V OC does not map to a unique. Step 1: Create the Dataset. = yk +sδk. Please keep in mind that interpolation is a statistical and mathematical tool that is used to predict the. The first parameter, NewX is simply the value to interpolate. Interpolation is the process of estimating an unknown value of a function between two known values. It keeps your code clean, short, tidy and. This is clearly a linear function that passes through (xk,yk) and (xk+1,yk+1). However, Excel does not provide an interpolation feature. x2 x 2 and y2 y 2 are the second coordinates. This equation will help you for interpolation. The domain of s is in intervals of [a, b]. Nov 21, 2022 at 21:06. Furthermore, scroll down and check the option ‘ Display Equation on chart ’. By mike0123m in forum Excel Formulas & Functions Replies: 8 Last Post: 09-28-2007, 02:09 PM. n) lies on the line (AC) between the two known rates. 399. We will show you 2 ways. Using the Point-Slope formula, where the slope is (y 2-y. I'm trying to construct a workbook with the ability to interpolate between 2 values. Video transcript. So, to use the GROWTH function: Plot the data table into your Excel spreadsheet. Alternatively,. Originally Posted by shg. To find the required y, type the equation above in an Excel cell. There's a linear interpolation (by computing fractions), but my recent try on logarithmic interpolation sometimes produces worse results than the linear one. Here, we create a straight line that connects two values and estimates the future value. ★ Want to automate Excel? Check out our training academy ★ Download the example file★ Formula: Here, (x1, y1) = The First coordinate of the interpolation process. 1. Interpolate values from column B and row 2. The formula in B2 and down is =2*LN (A2) + 1. Steps: First of all, let’s assume Depth of Flow = X and Discharge = Y. The entered value of the known_ys argument in cells “B3:B10”. The syntax for the FORECAST. And here it is the definitive 1 Excel to perform Linear Interpolation (single entry tables) and Bilinear Interpolation. INTERCEPT Function. So if you have 2-d table, you can find out which formula fits best your data, using Excel's "trend line" feature. 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. The data is set up as follows. Disappointing. LINEAR function predicts a value based on existing values along a linear trend. Step 4: Determine Interpolate Y Value Using User-Defined Function in Excel. In reply to Day William's post on August 3, 2022. As a result, when we apply this algorithm to the provided dataset, we receive the following interpolated y value: Interpolated value. If you are not committed to Excel, you could try Quattro Pro that does have a built in linear interpolation (@LINTERP()) function. For detailed instructions on how to do nonlinear interpolation within an Excel. This is the linear interpolation function: Y=Y1+ (X-X1)x (Y2-Y1)/ (X2-X1)Pairwise linear interpolation using FORECAST Curve-fitting interpolation using LINEST and SERIESSUM These are demonstrated in the following image (click to open in a new window for a more-readable view). Skip to main content. linear interpolation, which assumes developmentproportional to time over the period. The formula for bilinear interpolation is: This is a complicated formula, but it can be broken down. Then, we will select the cells containing the x and y coordinates where we want to interpolate. x y 1 10 2 15 3 20. 5 (answer available thru FORECAST but incomplete)I am not sure how to even approach or identify the function or result I would like to achieve. Bilinear interpolation - VBA code to be put in a module. Browser ini sudah tidak didukung. Right-click on one of the data points in the chart and select Add Trendline. By jsuarez199 in forum Excel Formulas & Functions Replies: 12 Last Post: 10-21-2013, 03:12 PM. In this article, we will show you two. We called out the WorksheetFunction Index and Match. Remember that likely none of us on this side of the internet has done this sort of calculation. It will add a. e. Then, insert two columns named LogX. . This is the lower value for interpolation. Originally Posted by shg. 5. ET MR Function Dictionary. Select the range of 12 data points for the X Function Argument field. What does this add-in do? It provides you with 2 new custom Excel functions: 1. 0. Download the Excel file for details. , x argument value. We have an experiment which have the variables x and y. Adding the module. Linear Interpolation - Key takeaways. 2. In Excel, we can use linear interpolation with functions and formulas. With this information the function will perform linear interpolation to find the specified value, or extrapolate to find the Y value if the X value is outside the range of known X values. 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. Now, we can see a new sidebar named ‘ Format Trendline ’. Copy the code below into the module. Step 1: Open Visual basic Editor. what value should be returned if the X value interpolated against is outside the range of known X values, (argument cutOffAction, 0 = extrapolate; 1 = cut off. In linear interpolation, you might get a new data point from two known data points. Another technique uses piecewise linear interpolation. TREASURY BOND YIELD CURVE by Andrew Chan Excel offers many powerful mathematical and. In this case first of all, you need to find closest larger and closest lower x values: Lower x: =MAX (IF (B1:L1<B5,B1:L1)) Larger x: =MIN (IF (B1:L1>B5,B1:L1)) Now need to find corresponding y's with HLOOKUP. Resize (N, 2) ' get x values (2 columns) x = rx. 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. If it's of an use, the code is: Code: Function VINTERP (mbr_age As Double, fac_range As Range, _ fac_offset As. Here, we have two variables, i. . 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). 2) a lookup function to return the slope and intercept specific to the desired unkown. It is a polynomial expression of the function f at the nth degree (x). A linear interpolation function is also included. It is usually applied to functions sampled on a 2D. Vector xq contains the coordinates of the query points. 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. 3 General Form of Newton’s Interpolating Polynomials The preceding analysis can be generalized to fit an nth-order polynomial to ( + 1) data points. 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. 15(entered in cell I1) =FORECAST. Lompati ke konten utama. In this first method, we will extrapolate 2. But it will become a pretty long formula, that is hard to read and/or copy across. Gary''s Student. First, organize your data into two columns with known values. In general, an interpolant need not be a good approximation, but there are well known and often reasonable conditions where it will. Mathematics is the native language of the natural world. Here, we have two variables, i. The symbols N (P) and N (Py) will denote the norms of P and Py. To create a sample linear interpolation formula. I assume that you want linear interpolation between 2 x values. Linear interpolation on a set of data points (x0, y0), (x1, y1),. 5 × 20000 = 30000. linear interpolation of sparse data I have several lists of data, each sampled at different varying intervals. Plugging these numbers into the linear-interpolation formula gives us an estimated yield to maturity of 8. First row and column must be sorted in ascending or descending order (see following picture). 2. @BruceWayne The formula is for simple linear interpolation. Linear Interpolation Formula is the process of finding a value between two points on a line or curve. Linear interpolation is used to find an unknown value of a function between any two known points. value Excel Linear #Interpolation using the #FORECAST built-in function#excel #linearinterpolation #math #finance #accounting #science #engineering When you are doing a linear interpolation, you are approximating the function between points as a line. The main factor about spline is that it combines. But in excel I don't even know where to start, maybe. The formula works like this:You could do a linear interpolation, which I describe in a tutorial called Excel Interpolation Formulas on my blog. 1. 3 is 5. Jul 16, 2003. For example, if B3=20 and and B4=30, then looking-up in the table, B5=9; b) when the value inputted in cell B3 OR B4, is NOT on the row or column heading, the value that appears in the row or column heads would just be "looked up" in the table, and the other one will involve linear interpolation to compute for the volume. This video looks at two problems in PTC Mathcad addressed w. E. The examples below assume that you know at least two points (x 1,y 1) and (x 2,y 2) and want to solve for y given x based on the equation for a line: y=mx+b, where m is the slope and b is the y-intercept. Next, double-click on the trend line. I successfully expanded the X-values to 1000 rows by splitting the list at the MIN value. Think of this as “the long-term estimate” of the data. Hello, I'm not sure that I understand you correct what you mean with "non-linear interpolation". known_ys:. , (xn, yn) is defined as the concatenation of linear interpolants between each pair of data points. The calculator plots the interpolated point on a line and shows a step-by-step solution by using linear interpolation formula. Because, as per the input data, the values corresponding to x = 1 is 1. Along the top ribbon in Excel, go to the Data tab and click on Data Analysis. ETS (target_date, values, timeline, [seasonality], [data_completion], [aggregation]) The FORECAST. The linear interpolation formula is the simplest method that is used for estimating the value of a function between any two known values. This means that the V OC does not map to a unique. The latest version of SRS1 Cubic Spline for Excel. Comprehending Interpolation. Nonlinear Interpolation With Excel To Construct U. Use INTERPXY to map a scattered (x,y) data points onto a uniform grid for easy plotting in Excel. Jun 12, 2013. So the formula is y = y1 + (x-x1) (y2-y1)/x2-x1 . Important: the positions and the separators must be respected (";" or "," according to Excel or Windows configuration ). But this formula will use the whole. Interpolation is the process of estimating an unknown value of a function between two known values. In this formula, (x1, y1) is referring to the first coordinate of the interpolation process. b. Formula implementation of interpolation. 27 3M 0. In MS Excel, press Alt+F11 to open the Visual Basic Editor (Windows). 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. - So, we've seen two ways to calculate in-betweens: linear interpolation and Bézier curves. Log-linear would be wrong; forward points are commonly negative, and are merely a delta on the Spot. 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. Function LinInterpolation(DataX As Range, DataY As Range, point As Range) As Variant: If point. To execute a linear interpolation in Excel, we use the forecast function and the lookup function to create a straight line that connects two values and estimates the future value through it. If you're stuck working in Excel. Piecewise linear interpolation. I don't know about other spreadsheets, but Microsoft just doesn't seem to want to put a linear interpolation function into Excel. In simple terms, linear interpolation is a method of estimating a value between two known data points by assuming that the relationship between the two data points is linear or straight-line. In the Format Trendline pane, select the options to Display. linear interpolation 2-way table. I will include the code below. So, it would be best to use this function. The function to be interpolated is known at given points and the interpolation problem consists. This will work on Excel 2007, but =FORECAST. If we had instead. Excel has an in-built function called INTERPOLATE that you can use to perform interpolation. 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. See the Excel file for details. Enter the formula =Interpol2D (y, x) into your spreadsheet where you want the interpolation result displayed. That is, it is recalculated every time any cell in any worksheet in the workbook is edited, as well as after certain other operations. Example of bilinear interpolation on the unit square with the z values 0, 1, 1 and 0. Even better, attach the Excel file that resulted in estimate-y of 1. LINEAR must be changed to =FORECAST (depreciated soon). To use these functions for linear interpolation, in our example, type the following equation in a cell: =SLOPE(C2:C15,B2:B15)*F2+INTERCEPT(C2:C15,B2:B15). richthorpe, this post is unlikely to be very useful for your purposes, but you may get some interest from it. Bessel and OneWay (monotonic) spline functions provide a more constrained smooth fit to data. In my career, about 90% of this work has involved batteries (always nonlinear) or sensors (frequently nonlinear). Excel - automatically fill down a row with date range matching a specific pattern. The functions of this Add-in are very simple to use and they have context help, through a chm file. As we can see from the image above, point P is situated in the rectangle defined by the values 22. Update the input column from D to E. Use this value in the INDEX function to return the values of jc0, y0, and^i, as shown in the. This is accomplished by the linterp function described below. Step 5: Display Chart Data for Cubic Spline Interpolation in Excel. Linear interpolation, which is also called simply interpolation, thus is the ability to. The formula in B17 is:2 – Convert your Data into a Table. In this method, we will use a mathematical formula to calculate logarithmic interpolation in Excel. 3D (Surface) Interpolation Use INTERPXYZ to interpolate from a set of (x,y,z) data points. Apr 22, 2021. If you think your data is linear, and you want to try the approach identified by @tom10, try. etc. The FORECAST worksheet function can be used to do an extrapolation of future data points based on a linear regression of existing data. gives. 1. For the 32 bits version of Excel select XonGrid. You want to obtain the value (by linear interpolation) for height of 7 and distance of 20. How does Excel compute its smoothing algorithm for curved lines in the XY scatter plots?New_y’s = InterpolateFunction(known_y’s, known_x’s, new_x’s), where Function relates to the equation used to fit the two known data points provided. Function approximation: Linear interpolation can be used to approximate a function based on a set of. The following Microsoft Excel formula performs linear interpolation by calculating the interpolation step value:. 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. All you have to do is to type in (or paste in) the concentrations of the standard solutions and their instrument readings (e. 31 6M 0. 5 in cell B5 to indicate that we want to find the midpoint. For example, Code: A --B-- 1 x y 2 1 1. If you have some idea of the shapes of the underlying yield curves, you can work. 5 as indicated. To help understand the formula you can also use the evaluate formula option under formulas menu in the Formula Auditing section (3rd or 4th section on the ribbon) Attached Files Interpolation formula. Polynomial. DrGenius. 4. To perform the Linear Interpolate calculations, we use the FORECAST Excel formula. 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. First, the. Description. Arius provides functions for the Exponential, Inverse Power, Linear, and Weibull curves. Do Interpolation of Non-Linear 2D Data in Excel. There are a few different types of interpolation techniques. Write down the numbers that you are going to put. , "eyeballed"). It would output the linear interpolation of the. a. Next, select the cell where you want the interpolated value to appear and use the formula =FORECAST. It gives the y-intercept value where the linear regression line intersects the y-axis. 3) simple algebra to compute the desired unkown. Step 2: Fill the formula with the desired values. Follow the steps given below to do it on your own dataset. Piecewise linear interpolation Figure 3. I took 2 screenshots. This uses Excel Forecast function =FORECAST (x,known_y's,known_x's). Cubic Spline Method: This method fits a distinct cubic. g. 5 Then Stop nR = rX. So the formula for linear interpolation is y = y1 + (x-x1) (y2-y1)/ (x2-x1). We’ve added the value 0. 34 1Y 0. I have a very specific excel problem. The three mandatory arguments of the FORECAST Excel formula are,. Here, for this dataset, you can use the TREND function of Excel. Generating piecewise linear approximations for non-linear curves is a very common task. However, there is a straightforward technique. In this case first of all, you need to find closest larger and closest lower x values: Lower x: =MAX (IF (B1:L1<B5,B1:L1)) Larger x: =MIN (IF (B1:L1>B5,B1:L1)) Now need to find corresponding y's with HLOOKUP. Verma @ 1 person found this reply helpful. For instance, Bresenham's algorithm interpolates points incrementally between the two endpoints of a line (Bresenham, J. To interpolate the missing value in cell B4 using linear interpolation: In an empty cell (e. If your dates are formatted appropriately, Excel just reads them as numbers, so this will work fine. 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. 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. The following Microsoft Excel formula performs linear interpolation by calculating the interpolation step value: =(end-start)/(ROW(end)-ROW(start)) where end. However, sometimes actuaries are more comfortable with the assumption that more will be reported or paid. forecast uses simple linear regression to predict a new y value based on known y and known x values. This tip explains how to use the function. Select the chart to see two new tabs in the ribbon, “Design” and “Format. If you can express your problem as a system of linear equations, excel has a few matrix formulas. LINEAR calculates future value predictions using linear regression, and can be used to predict numeric values like sales, inventory, test scores, expenses, measurements, etc. The GROWTH function is a special function to interpolate non-linear data. Function LinInterpolation(DataX As Range, DataY As Range, point As Range) As Variant: If point. Renaming an Excel Table Name. From the iteration calculations so far, we know that at the lower discount rate rL of 8. In this case, F7=7 and E7=3 because B already included the integer 7 and has a matching value in column A. However, F8=8, which is an intermediate value not contained in column B. S. When the data is not linear, the first thing we have to do is to find out the function's behavior. I want to interpolate two values in Excel. if x=1. How to find that function? (among a, b, c; a is the number I need the most). Copy the value in C1 and paste it in cell B4 to replace the missing value. It fits a straight line (using the method of least squares) to the array's known_y's and known_x's. Create the Linear Interpolation VBA Function. 773 6 5 4. 41 3Y 0. Linear interpolation is often not accurate for non-linear data. I had to use multiple "Match" and "Index" Method to do the "linear interpolation" for myself. 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. Option Explicit 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. To interpolate this value, use the following simple formula: (x – x1) * y= y1 + (x – x1) x=22. User case. If developer tab is not available in your Excel, you need to go to Files > Options, click Customize Ribbon and check the box. 5/19/10 4:47 PM. What I want is to rewrite the linear interpolation function so that it can calculate. as in following example. The Y- coordinates even start descending and ascending multiple times. 1. For interpolation, we have a below formula. The functions are accessed just like any other standard Excel function. , Algorithm for computer control of a digital plotter, IBM Systems Journal, Vol 4, No 1, (1965), pp 2530. As the name implies it fits a straight line to each. Lec. linear(a2,c2:d2,e2:f2),na())which is the formula for linear interpolation in the interval (,). You have to use it as native function of Google Sheet 1. Whether you're new to Microsoft Excel or simply looking to pick up a few new tips and tricks, you're sure to enjoy this free software tutorial. Define a matching set of x values. 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. First, you need the data graph so Excel can fit a line to the data and g. , see screenshot: Step 3: In Fill Blank Cells dialog box, check the Linear values option and From left to right option. Hi there, I'm in search for a formula that can perform linear interpolation between within arange of values. 5 Then Stop nR = rX. 10. Cell A2: Copy your weight data into the first column. 5 in this case. Virtually every software comes with a function to perform linear. ”. As a result, when we apply this algorithm to the provided dataset, we receive the following interpolated y value: Interpolated value. Select the row of x-values in the data table and name those “xvalues”. The above formula is the Linear Interpolation Formula. 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. 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. Functions like this are known as UDFs, or User Defined Functions. Then, insert two columns named LogX and LogY. which would have target achievement values from 101% to X%, and corresponding bonus award amounts ending. We seek to find the Y value for an arbitrary X value between these two points, such that it is on the straight line that is drawn between them.