L_LINTERP
=L_LINTERP(x, known_xs, known_ys)
Argument | Description | Example |
---|---|---|
x | Value or vector of values to be interpolated | 2 |
known_xs | The known x values | {2;3;4} |
known_ys | The corresponding known y values | {0.9;0.14;-0.76} |
Description
L_LINTERP uses linear interpolation between the two nearest points in a table lookup to return the value y for a given x. If the value for x is outside the domain of known xs, then the point is extrapolated from the closest two data points (either the first or last 2 points).
The known (x,y) pairs do not need to be sorted because the function sorts the points prior to doing the table lookup. x can be a vector of x values or a single value.
A line is defined by two points: (x1,y1) and (x2,y2). Linear interpolation calculates a y0 value for a given x0 value using the formula:
y0 = y1+(x0-x1)*(y2-y1)/(x2-x1) y0 = SLOPE({y1;y2},{x1;x2})*x0+INTERCEPT({y1;y2},{x2;x2})
Lambda Formula
This code for using L_LINTERP in Excel is provided under the License as part of the LAMBDA Library, but to use just this function, you may copy the following code directly into your spreadsheet.
Code to Create Function via the Name Manager
Name: L_LINTERP
Comment: Linearly interpolate between the two nearest points
Refers To:
=LET(doc,"https://www.vertex42.com/lambda/linterp.html",
xs,IF(AND(ROWS(xs)=1,COLUMNS(xs)>1),TRANSPOSE(xs),xs),
known_xs,IF(AND(ROWS(known_xs)=1,COLUMNS(known_xs)>1),TRANSPOSE(known_xs),known_xs),
known_ys,IF(AND(ROWS(known_ys)=1,COLUMNS(known_ys)>1),TRANSPOSE(known_ys),known_ys),
tab,SORT(HSTACK(known_xs,known_ys)),
BYROW(xs,LAMBDA(x,
LET(ind,MATCH(x,INDEX(tab,0,1),1),
pts,
IF(x<=MIN(known_xs),TAKE(tab,2),
IF(x>=MAX(known_xs),TAKE(tab,-2),
CHOOSEROWS(tab,ind,ind+1)
)
),
SLOPE(INDEX(pts,,2),INDEX(pts,,1))*x+INTERCEPT(INDEX(pts,,2),INDEX(pts,,1))
)
))
));
Code for AFE Workbook Module (Excel Labs Add-in)
/** * Linearly interpolate between the two nearest points in a table lookup */ L_LINTERP = LAMBDA(xs,known_xs,known_ys, LET(doc,"https://www.vertex42.com/lambda/linterp.html", xs,IF(AND(ROWS(xs)=1,COLUMNS(xs)>1),TRANSPOSE(xs),xs), known_xs,IF(AND(ROWS(known_xs)=1,COLUMNS(known_xs)>1),TRANSPOSE(known_xs),known_xs), known_ys,IF(AND(ROWS(known_ys)=1,COLUMNS(known_ys)>1),TRANSPOSE(known_ys),known_ys), tab,SORT(HSTACK(known_xs,known_ys)), BYROW(xs,LAMBDA(x, LET(ind,MATCH(x,INDEX(tab,0,1),1), pts, IF(x<=MIN(known_xs),TAKE(tab,2), IF(x>=MAX(known_xs),TAKE(tab,-2), CHOOSEROWS(tab,ind,ind+1) ) ), SLOPE(INDEX(pts,,2),INDEX(pts,,1))*x+INTERCEPT(INDEX(pts,,2),INDEX(pts,,1)) ) )) ));
Named Function for Google Sheets
Name: L_LINTERP Description: Linearly interpolate between the two nearest points Arguments: xs, known_xs, known_ys Function: LET(doc,"https://www.vertex42.com/lambda/linterp.html", xs,IF(AND(ROWS(xs)=1,COLUMNS(xs)>1),TRANSPOSE(xs),xs), known_xs,IF(AND(ROWS(known_xs)=1,COLUMNS(known_xs)>1),TRANSPOSE(known_xs),known_xs), known_ys,IF(AND(ROWS(known_ys)=1,COLUMNS(known_ys)>1),TRANSPOSE(known_ys),known_ys), tab,SORT(HSTACK(known_xs,known_ys)), BYROW(xs,LAMBDA(x, LET(pts, IF(x<=MIN(known_xs),CHOOSEROWS(tab,{1;2}), IF(x>=MAX(known_xs),CHOOSEROWS(tab,{ROWS(tab)-1;ROWS(tab)}), CHOOSEROWS(tab,SEQUENCE(2,,MATCH(x,INDEX(tab,0,1),1))) ) ), SLOPE(INDEX(pts,0,2),INDEX(pts,0,1))*x+INTERCEPT(INDEX(pts,0,2),INDEX(pts,0,1)) ) )) )
Google Sheets doesn't have a TAKE function (yet?), so the GS function uses CHOOSEROWS instead.
L_LINTERP Examples
Test: Copy and Paste this LET function into a cell =LET( xs, L_LINSPACE(1.5,9.5,9), known_xs, L_SE(2,9), known_ys, SIN(known_xs), L_LINTERP(xs,known_xs,known_ys) ) Result: {1.293;0.525;-0.308;-0.858;-0.619;0.189;0.823;0.701;0.123}
Test: Copy and Paste this LET function into a cell =LET( x, 3, known_xs, {2;4}, known_ys, {3;2.5}, L_LINTERP(x,known_xs,known_ys) ) Result: 2.75