Interpolation of an xy table

[trent172]

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
14%	¥1,515	¥1,750	¥1,981	¥2,207	¥2,428	¥2,644	¥2,855
15%	¥1,428	¥1,650	¥1,867	¥2,080	¥2,288	¥2,492	¥2,691
16%	¥1,351	¥1,561	¥1,767	¥1,968	¥2,166	¥2,358	¥2,547
17%	¥1,284	¥1,483	¥1,679	¥1,870	¥2,058	¥2,241	¥2,420
18%	¥1,224	¥1,414	¥1,601	¥1,783	¥1,962	¥2,136	¥2,307

<tbody>
</tbody>

For example, if 11.6% and 520 were the results achieved, I need to figure out what the weighted average is between 11% and 12% whilst at the same time the weighted average between 500 and 550 to arrive at a final exact figure. In this example I believe the answer should be Y2968 (rounded to the nearest yen).

Basically I need to be able to calculate any data point in between the known xy figures above.

Thanks in advance

 

[DRSteele]

My investigations lead me to believe that what you ask is almost impossible with functions and formulas. It is possible to do interpolation for one row at a time or one column at a time, but doing both at once boggles the mind.

The data is not simple-linear. What you need is a User Defined Function, which has been dealt with in this forum.

See here: http://www.mrexcel.com/forum/excel-...-interpolate-simple-linear-interpolation.html

 

[Eric W]

DRSteele is correct, the data are not linear. Trying to come up with a function (formulas or UDF) that "best" interpolates the data is very difficult. I tried using your data and the FORECAST function, and the results were all over.

However, if you are content with simple linear interpolation between points, it's much simpler (but still not "simple"). Consider this sheet:

	A	B	C	D	E	F	G	H	I	J	K
1		300	350	400	450	500	550	600		PCT	Yen
2	11%	1872	2163	2448	2727	3000	3267	3528		11.60%	520
3	12%	1733	2002	2266	2525	2777	3025	3266
4	13%	1616	1867	2113	2353	2589	2820	3045			Amt
5	14%	1515	1750	1981	2207	2428	2644	2855			2968.44
6	15%	1428	1650	1867	2080	2288	2492	2691
7	16%	1351	1561	1767	1968	2166	2358	2547		2	6
8	17%	1284	1483	1679	1870	2058	2241	2420
9	18%	1224	1414	1601	1783	1962	2136	2307		3000	3267
10										2777	3025
11										500
12										550
13										0.4
14										2866.2	3121.8
15
16										0.11
17										0.12
18										0.6

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet6​

Worksheet Formulas Cell Formula K5 =J14+(K14-J14)*J13 J7 =MATCH(J2,A1:A9) K7 =MATCH(K2,A1:H1) J9 =INDEX(A1:H9,J7,K7) K9 =INDEX(A1:H9,J7,K7+1) J10 =INDEX(A1:H9,J7+1,K7) K10 =INDEX(A1:H9,J7+1,K7+1) J11 =INDEX(A1:H1,1,K7) J12 =INDEX(A1:H1,1,K7+1) J13 =(K2-J11)/(J12-J11) J14 =J9+(J10-J9)*J18 K14 =K9+(K10-K9)*J18 J16 =INDEX(A1:A9,J7) J17 =INDEX(A1:A9,J7+1) J18 =(J2-J16)/(J17-J16) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

Put the formulas in as shown. (I started to make a 1-cell formula combining all those, but gave up as it would be extraordinarily long and complicated.) Then put the percent and amount in the J2:K2 cells. The example shown gives a result of 2968, which is what you got, leading me to believe that you calculated it the same way.

Let me know if this helps.

 

[Eric W]

Using the FORECAST function, I did manage to reduce the number of formulas:

	A	B	C	D	E	F	G	H	I	J	K
1		300	350	400	450	500	550	600		PCT	Yen
2	11%	1872	2163	2448	2727	3000	3267	3528		11.60%	520
3	12%	1733	2002	2266	2525	2777	3025	3266
4	13%	1616	1867	2113	2353	2589	2820	3045			Amt
5	14%	1515	1750	1981	2207	2428	2644	2855			2968.44
6	15%	1428	1650	1867	2080	2288	2492	2691
7	16%	1351	1561	1767	1968	2166	2358	2547		2	6
8	17%	1284	1483	1679	1870	2058	2241	2420		2866.2	3121.8
9	18%	1224	1414	1601	1783	1962	2136	2307

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet6​

Worksheet Formulas Cell Formula K5 =FORECAST(K2,J8:K8,OFFSET($A$1:$B$1,0,$K$7-1)) J7 =MATCH(J2,A1:A9) K7 =MATCH(K2,A1:H1) J8 =FORECAST(J2,OFFSET($A$1,$J$7-1,$K$7-1,2,1),OFFSET($A$1:$A$2,$J$7-1,0)) K8 =FORECAST($J$2,OFFSET($B$1,$J$7-1,$K$7-1,2,1),OFFSET($A$1:$A$2,$J$7-1,0)) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

Since I only use the 2 adjacent data points in the formulas, they come out as linear.