XLOOKUP Interpolation

[RicardoP]

Dear Excel users,

I need to search and interpolate along a discrete Y(X) function that can have two possible X solutions.

I am trying to use the XLOOKUP, however the search_mode does not return both solutions.

Can you please help me?

Interpolation.png

X
0.22
0.23
0.24
0.25
0.26
0.27
0.65
0.66
0.67
0.68
0.69
0.70

Y
1566.54
1582.44
1597.44
1611.56
1624.82
1637.23
1627.06
1618.02
1608.73
1599.20
1589.45
1579.50

Kind Regards,
Ricardo

[windknife]

Maybe

D1=1600
E1
=IFERROR(LET(p,AGGREGATE(15,6,ROW($B$2:$B$100)/(($B$2:$B$100<=$D$1)*($B$3:$B$101>$D$1)+($B$2:$B$100>=$D$1)*($B$3:$B$101<$D$1)),COLUMNS($E$1:E1)),($D$1-INDEX($B:$B,p))/(INDEX($B:$B,p+1)-INDEX($B:$B,p))*(INDEX($A:$A,p+1)-INDEX($A:$A,p))+INDEX($A:$A,p)),"")

copied right.

[RicardoP]

Hello windknife,

Thank you very much for you prompt reply in helping me, really appreciated.

You've 'cut' this problem very swiftly indeed!

May I ask if it would be possible for you to explain that formula to me in order to apply it into different scenarios?

Cheers,
Ricardo

[windknife]

Simple explanation as follows

1. find the row (using p denote) which satisfies y1 <= y3 < y2 or y2 <= y3 < y1

==> B(p)=y1, B(p+1)=y2, A(p)=x1, A(p+1)=x2, D1=y3

==> B(p) <= D1 < B(p+1) or B(p+1) <= D1 < B(p)

AGGREGATE(15,6,ROW($B$2:$B$100)/(($B$2:$B$100<=$D$1)*($B$3:$B$101>$D$1)+($B$2:$B$100>=$D$1)*($B$3:$B$101<$D$1)),COLUMNS($E$1:E1))

2. using interpolation formula

x3 = (y3-y1)/(y2-y1)*(x2-x1)+x1

===> ( D1 - B(p) ) / ( B(p+1) - B(p) ) * ( A(p+1) - A(p) ) + A(p)

($D$1-INDEX($B:$B,p))/(INDEX($B:$B,p+1)-INDEX($B:$B,p))*(INDEX($A:$A,p+1)-INDEX($A:$A,p))+INDEX($A:$A,p)),"")

[RicardoP]

Hello again windknife,

Would it be possible for you to change the formula in order to use the X and Y columns as an Excel table?

Kind regards,
Ricardo

[RicardoP]

Hello windknife,

Thanks for explaining the formula to me.

I am trying to implement your formula in my X and Y data that is an Excel table.

The task is reveling difficult because I do not know how to use the Excel structure references to point to the first row, last, penultimate and ante-penultimate rows.

I would be so very much thankful if you could change the formula in order to be used with Excel tables?

Kind regards,
Ricardo

[windknife]

I used data table which table name is [data] and reorganized formula as follows,

E1

=IFERROR(LET(
p,AGGREGATE(15,6,ROW($B$2:$B$100)/(($B$2:$B$100<=$D$1)*($B$3:$B$101>$D$1)+($B$2:$B$100>=$D$1)*($B$3:$B$101<$D$1)),COLUMNS($E$1:E1)),
x_1, INDEX(Data[[X]:[X]],p), 
y_1, INDEX(Data[[Y]:[Y]],p), 
x_2, INDEX(Data[[X]:[X]],p+1),
y_2, INDEX(Data[[Y]:[Y]],p+1),
y_3, $D$1,
(y_3-y_1)/(y_2-y_1)*(x_2-x_1)+x_1
),"")

copied right.

[RicardoP]

Dear windknife,

Thank you again for such a quick reply.
You are being so helpful, thanks for your patience in helping me.


When I try to move the table and the interpolation cells, the results can change or be lost.


I will use your fantastic Search&Interpolate method to calculate across a table that is composed by
X1 Y1 , X2 Y2 , X3 Y3 , ... , Xn Yn

Therefore it would be great to be able to drag your formula to collect the results across that big table.

Do you think that it would be possible to recreate the AGGREGATE to use Excel structured references from the Excel table?


I am sorry for asking you so many requests, however your work is so very much welcome, furthermore I really do not know how to thank you properly for all your efforts...

Kindest Regards,
Ricardo

[windknife]

Sorry, this is beyond my ability. Maybe, you can use VBA to solve it.

[RicardoP]

Hello windknife,

I am so sorry for such a late reply, I was very busy at work and at home as well.

Regarding my last reply, basically would be updating your formula to fully refer to an excel table.

This would allow moving your formula and the table to any place without breaking the formula.

That is why I asked if it would be possible for you to update the formula to fully use the data just from the Excel table.

I tried to change your formula.
However the task is very difficult.
That is because I do not know how to use the Excel table structure references to:

a) get only the first row.
b) get only the last row.
c) get only the penultimate row.
d) get only the ante-penultimate row.

If I could know how to update your formula would be so helpful.

windknife, please would you be so kind to have another look and see if you can rearrange your formula to only use Excel structure references?

Kindest Regards,
Ricardo

[windknife]

I don't sure what you want, maybe

E2
=IFERROR(LET(p,AGGREGATE(15,6,ROW($B$2:$B$105)/(($B$2:$B$105<=$D$1)*($B$3:$B$106>$D$1)+($B$2:$B$105>=$D$1)*($B$3:$B$106<$D$1)),ROWS($E$2:E2)),x_1,INDEX(Data[[X]:[X]],p), y_1,INDEX(Data[[Y]:[Y]],p), x_2, INDEX(Data[[X]:[X]],p+1),y_2, INDEX(Data[[Y]:[Y]],p+1),y_3,$D$1,(y_3-y_1)/(y_2-y_1)*(x_2-x_1)+x_1),"")

copied down


a) get only the first row.

=INDEX(E:E,2)

b) get only the last row.

=INDEX(E:E,LOOKUP(2,1/(E2:E100<>""),ROW(E2:E100)))

c) get only the penultimate row.

=INDEX(E:E,LOOKUP(2,1/(E2:E100<>""),ROW(E2:E100))-1)

[RicardoP]

Thank you windknife.
I will try that!

Let me try to explain better:

Instead of, for example, having this way that follows of getting the data:
$B$2:$B$105

Make use of Excel table structure references instead, like for example:
Data[[ applicable $B$2:$B$105 ]:[ applicable $B$2:$B$105 ]]


The idea is having all the manual cell references (like $B$2:$B$105) transformed into Excel table structure references.

Is it something that it would be possible for you to accomplish please?

Warm Regards,
Ricardo