Table interpolation

[ponedelj]

Hi. So im looking for formula to interpolate X and Y axis and to find interpolated value in table.

For example on top row 1.5 and side colum1.77 should give interpolate value in the table.

 

[ponedelj]

No. Its possible the value is not in table. It need to calculate between. For example X=1.453 and Y=1.753.
Thats why this makes everything very complex

 

[dreid1011]

No. Its possible the value is not in table. It need to calculate between. For example X=1.453 and Y=1.753.
Thats why this makes everything very complex

What calculation should it perform then if the values are not in the table?

 

[ponedelj]

Use this to first find values on Y axis and same but only turned to Row to find X value. But for that i need new table. Maybe there is easier way? Also this formula dont work so well if its 0 or max value


=FORECAST(C3,OFFSET(interpol_array,MATCH($C3,lookup_array,1)-1,0,2),OFFSET(lookup_array,MATCH($C3,lookup_array,1)-1,0,2))

 

[dreid1011]

Can you post a sample of your data? Or is it the table you already posted? The formula by itself is not enough for me to go on.

 

[Eric W]

I did something like this a long time back, see here:

Interpolation of an xy table

Hi All, Looking for a formula to work out the exact amount to be charged in the below cost table 300 350 400 450 500 550 600 11% ¥1,872 ¥2,163 ¥2,448 ¥2,727 ¥3,000 ¥3,267 ¥3,528 12% ¥1,733 ¥2,002 ¥2,266 ¥2,525 ¥2,777 ¥3,025 ¥3,266 13% ¥1,616 ¥1,867 ¥2,113 ¥2,353 ¥2,589 ¥2,820 ¥3,045...

www.mrexcel.com

I tried to update it with newer functions, but it still came out pretty complicated:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1		1	1.1	1.2	1.3	1.4	1.5	1.6	1.7	1.8	1.9		X	Y	Value
2	1.7	1.7	1.87	2.04	2.21	2.38	2.55	2.72	2.89	3.06	3.23		1.42	1.775	2.5347
3	1.71	1.71	1.881	2.052	2.223	2.394	2.565	2.736	2.907	3.078	3.249
4	1.72	1.72	1.892	2.064	2.236	2.408	2.58	2.752	2.924	3.096	3.268
5	1.73	1.73	1.903	2.076	2.249	2.422	2.595	2.768	2.941	3.114	3.287
6	1.74	1.74	1.914	2.088	2.262	2.436	2.61	2.784	2.958	3.132	3.306
7	1.75	1.75	1.925	2.1	2.275	2.45	2.625	2.8	2.975	3.15	3.325
8	1.76	1.76	1.936	2.112	2.288	2.464	2.64	2.816	2.992	3.168	3.344
9	1.77	1.77	1.947	2.124	2.301	2.478	2.655	2.832	3.009	3.186	3.363
10	1.78	1.78	1.958	2.136	2.314	2.492	2.67	2.848	3.026	3.204	3.382
11	1.79	1.79	1.969	2.148	2.327	2.506	2.685	2.864	3.043	3.222	3.401
Sheet5

Cell Formulas
Range	Formula
O2	O2	=LET(x,XMATCH(N2,A2:A11,-1),f,CHOOSE({1;2},FORECAST(M2,OFFSET(B2:K2,x,0),B1:K1),FORECAST(M2,OFFSET(B2:K2,x+1,0),B1:K1)),FORECAST(N2,f,OFFSET(A2:A11,XMATCH(N2,A2:A11,-1)-1,0,2)))

You should manually calculate a few to be sure it's right. I got one of the calculations backward, but I think it's fixed. Essentially, the formula finds the 2 rows surrounding the Y variable (rows 9 and 10 in this example), and interpolates the values on those rows using the X variable. Then given those 2 results, it interpolates again, using those results and the Y variable and the values in A9:A10.

 

[dreid1011]

I did something like this a long time back, see here:

 

[ponedelj]

It should find the values in between. For example x=1.453 y=1.753
So for X1,Y1 1.4, 1.75 value are 2.45, for
X1,Y2 1.4, 1.76 value is 2.464,
X2,Y1 1.5,1.75 value is 2.625
X2,Y2 1.5,1.76 value is 2.64 so the Z value lies between thous 4 figures which are calculate by 3rd inerpolation.

(The values on X and Y axis are not related to Table, i mean u can not calculate Z by multiplying some X or Y value. Only by intepolation!)