Help developing a LAMBDA function to interpolate from a list

dejhantulip

New Member
Joined
Sep 9, 2015
Messages
41
Office Version
  1. 365
Platform
  1. Windows
Dear all,

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

I have the following scenario:

n8h1pE9.png


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
Joined
Feb 4, 2012
Messages
1,118
Office Version
  1. 365
  2. 2013
  3. 2010
Platform
  1. Windows
  2. Web
Please test the following:
Book1
ABCDEFGHI
1
2ROADSTATION CUMULATIVE VOLUME
37A00ROAD7A
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
Joined
Aug 18, 2015
Messages
10,976
Not a LAMBDA, but try:

Book1
ABCDEFGH
1
2RoadStationCumulative volume
37A00
47A2029.1Road7A
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
Joined
Sep 9, 2015
Messages
41
Office Version
  1. 365
Platform
  1. Windows
Please test the following:
Book1
ABCDEFGHI
1
2ROADSTATION CUMULATIVE VOLUME
37A00ROAD7A
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:

VOLUME(RangeThatHasTheValuesToBeInterpolated, RangeWithRoadCriteria, RoadNumber, RangeWithStationCriteria, StationNumber) right?

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
Joined
Feb 4, 2012
Messages
1,118
Office Version
  1. 365
  2. 2013
  3. 2010
Platform
  1. Windows
  2. Web

ADVERTISEMENT

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)))
 
Solution

dejhantulip

New Member
Joined
Sep 9, 2015
Messages
41
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Feb 4, 2012
Messages
1,118
Office Version
  1. 365
  2. 2013
  3. 2010
Platform
  1. Windows
  2. Web
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.
 

Watch MrExcel Video

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top