LINTERP - Linear Interpolation Function for Excel

Work in Progress :: Please contact us to report errors, typos, etc.

=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.

Warning

These functions are not compatible between Excel and Google Sheets. When using these functions, you will not be able to convert the Excel file to Google Sheets or vice versa without problems.

L_LINTERP Examples

Example 1

Generate the values shown in the example image above.

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}

Example 2

A line is defined by two points (x1,y1)=(2,3) and (x2,y2)=(4,2.5). Find the y value for the point where x=3.

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

L_LINTERP