Help developing a LAMBDA function to interpolate from a list

dejhantulip

New Member
Dear all,

I was wondering if someone would be kind enough to help me with the following.

I have the following scenario:

Where I have different roads in column B and the stations (locations along the road) in column C and finally the cumulative volume to be excavated up to each station in column D.

Basically what I want is a LAMBDA function that could be called XINTERPOLATE or something like that that takes the Road and the Station as inputs.
It would output the linear interpolation of the specified road between the specified stations (if the specified station is not exactly on the list) to get the corresponding volume at said station.

So basically in the screenshot:
Using the road data in cell G4 which is road 7A, I would like the volume at station 32 (cell G5).
In this case the result should be (in cell G6) equal to 45.61 using a simple linear interpolation equation.

I thank you very much in advance!

Have a great one!

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.

J.Ty.

Well-known Member
Book1
ABCDEFGHI
1
47A2039STATION41
57A4051VOLUME52.6
67A6083
77A80123
87A99162
91000
10102045
11104057
12106089
131080110
1410100160
1510120181
1610140199
1710160241
18
19
Sheet1
Cell Formulas
RangeFormula
H5H5=VOLUME(H3,H4)

where
Code:
``VOLUME``
is
Code:
``=LAMBDA(ROAD,STATION,LET(POS,MATCH(STATION,FILTER(Sheet1!\$C\$3:\$C\$17,Sheet1!\$B\$3:\$B\$17=ROAD)),((STATION-INDEX(Sheet1!\$C\$3:\$C\$17,POS))/(INDEX(Sheet1!\$C\$3:\$C\$17,1+POS)-INDEX(Sheet1!\$C\$3:\$C\$17,POS)))*(INDEX(Sheet1!\$D\$3:\$D\$17,1+POS)-INDEX(Sheet1!\$D\$3:\$D\$17,POS))+INDEX(Sheet1!\$D\$3:\$D\$17,POS)))``

Eric W

MrExcel MVP
Not a LAMBDA, but try:

Book1
ABCDEFGH
1
37A00
57A4056.61Station32
67A5080.13Cum. Vol. @ Station45.60649.13718
77A60104.41
87A99.82132.85
91000
10102045
11104057
12106089
131080110
1410100160
1510120181
1610140199
1710160241
Sheet4
Cell Formulas
RangeFormula
G6G6=FORECAST(G5,OFFSET(D1,AGGREGATE(14,6,ROW(D3:D31)/(B3:B31=G4)/(C3:C31<=G5),1)-1,0,2),OFFSET(C1,AGGREGATE(14,6,ROW(D3:D31)/(B3:B31=G4)/(C3:C31<=G5),1)-1,0,2))
H6H6=FORECAST(G5,FILTER(D3:D31,B3:B31=G4),FILTER(C3:C31,B3:B31=G4))

I don't have LET yet either, or I think I could shorten it. If you're willing to base the interpolation based on the entire range, rather than just the 2 nearest points, then the H6 formula is much easier.

dejhantulip

New Member
Book1
ABCDEFGHI
1
47A2039STATION41
57A4051VOLUME52.6
67A6083
77A80123
87A99162
91000
10102045
11104057
12106089
131080110
1410100160
1510120181
1610140199
1710160241
18
19
Sheet1
Cell Formulas
RangeFormula
H5H5=VOLUME(H3,H4)

where
Code:
``VOLUME``
is
Code:
``=LAMBDA(ROAD,STATION,LET(POS,MATCH(STATION,FILTER(Sheet1!\$C\$3:\$C\$17,Sheet1!\$B\$3:\$B\$17=ROAD)),((STATION-INDEX(Sheet1!\$C\$3:\$C\$17,POS))/(INDEX(Sheet1!\$C\$3:\$C\$17,1+POS)-INDEX(Sheet1!\$C\$3:\$C\$17,POS)))*(INDEX(Sheet1!\$D\$3:\$D\$17,1+POS)-INDEX(Sheet1!\$D\$3:\$D\$17,POS))+INDEX(Sheet1!\$D\$3:\$D\$17,POS)))``

Great!! Thank you very much!!

Do you think it would be possible to incorporate the following syntax:

In this case in cell G6 I would input: VOLUME(D3:D31, B3:B31, G4, C3:C31, G5)

See what I mean? Kind of like the SUMIFS syntax where you have a range to be sumed, and the criterias.

Thank you so very much!!

ORLANDO

J.Ty.

Well-known Member

Here you are:
Code:
``=LAMBDA(CUMULATIVES,ROADS,ROAD,STATIONS,STATION,LET(POS,MATCH(STATION,FILTER(STATIONS,ROADS=ROAD)),((STATION-INDEX(STATIONS,POS))/(INDEX(STATIONS,1+POS)-INDEX(STATIONS,POS)))*(INDEX(CUMULATIVES,1+POS)-INDEX(CUMULATIVES,POS))+INDEX(CUMULATIVES,POS)))``

dejhantulip

New Member
Here you are:
Code:
``=LAMBDA(CUMULATIVES,ROADS,ROAD,STATIONS,STATION,LET(POS,MATCH(STATION,FILTER(STATIONS,ROADS=ROAD)),((STATION-INDEX(STATIONS,POS))/(INDEX(STATIONS,1+POS)-INDEX(STATIONS,POS)))*(INDEX(CUMULATIVES,1+POS)-INDEX(CUMULATIVES,POS))+INDEX(CUMULATIVES,POS)))``

Amazing!! Thank you so very very much!

J.Ty.

Well-known Member
Thanks for the feedback and good luck!

Please note, however, that SUMIFS and similar built-in functions produce an error when used with ranges whose sizes are not euqal. My function does not include this functionality.

Replies
5
Views
235
Replies
4
Views
562
Replies
1
Views
170
Replies
1
Views
69
Replies
0
Views
163

1,133,521
Messages
5,659,303
Members
418,496
Latest member
WHYCHumphrey

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back