Help developing a LAMBDA function to interpolate from a list

[dejhantulip]

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!

 

[J.Ty.]

Please test the following:

Book1
	A	B	C	D	E	F	G	H	I
1
2		ROAD	STATION	CUMULATIVE VOLUME
3		7A	0	0			ROAD	7A
4		7A	20	39			STATION	41
5		7A	40	51			VOLUME	52.6
6		7A	60	83
7		7A	80	123
8		7A	99	162
9		10	0	0
10		10	20	45
11		10	40	57
12		10	60	89
13		10	80	110
14		10	100	160
15		10	120	181
16		10	140	199
17		10	160	241
18
19
Sheet1

Cell Formulas
Range	Formula
H5	H5	=VOLUME(H3,H4)

where

VOLUME

is

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

Not a LAMBDA, but try:

Book1
	A	B	C	D	E	F	G	H
1
2		Road	Station	Cumulative volume
3		7A	0	0
4		7A	20	29.1		Road	7A
5		7A	40	56.61		Station	32
6		7A	50	80.13		Cum. Vol. @ Station	45.606	49.13718
7		7A	60	104.41
8		7A	99.82	132.85
9		10	0	0
10		10	20	45
11		10	40	57
12		10	60	89
13		10	80	110
14		10	100	160
15		10	120	181
16		10	140	199
17		10	160	241
Sheet4

Cell Formulas
Range	Formula
G6	G6	=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))
H6	H6	=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]

Please test the following:

Book1
	A	B	C	D	E	F	G	H	I
1
2		ROAD	STATION	CUMULATIVE VOLUME
3		7A	0	0			ROAD	7A
4		7A	20	39			STATION	41
5		7A	40	51			VOLUME	52.6
6		7A	60	83
7		7A	80	123
8		7A	99	162
9		10	0	0
10		10	20	45
11		10	40	57
12		10	60	89
13		10	80	110
14		10	100	160
15		10	120	181
16		10	140	199
17		10	160	241
18
19
Sheet1

Cell Formulas
Range	Formula
H5	H5	=VOLUME(H3,H4)

where

VOLUME

is

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

Here you are:

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

Here you are:

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

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.