Sub for table interpolation

**potga** · 07-30-2014, 08:26 PM

Need to use interpolation to find intermediate values on a table that has around 7 x 20 entries.

Have an interpolation program on my calculator and I was using this to get results from a book of tables containing a few hundred pages. This requires entering the target x value the table x values either side of the target x value, same for the y values, and the 4 corresponding xy table values. 10 entries in total.

Now that I have a smaller table that I could put into Excel I want to setup a userform that only requires entry of the target x and y values to get the answer.

Any ideas how to do this? I can do the user form, etc. but doing the code for the interpolation is way above my ability.

**MrShorty** · 07-30-2014, 10:28 PM

I would suggest you search this forum. A few users (shg is one that I remember off the top of my head) have posted VBA code for performing linear interpolation that you should be able to incorporate into your project.

**shg** · 08-10-2014, 04:34 PM

	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
3		Temp
4	MW	-150	-154	-158	-160	-162	-166	-170
5	16.0	-0.000012	-0.000010	-0.000009	-0.000009	-0.000008	-0.000007	-0.000007
6	16.5	0.000135	0.000118	0.000106	0.000100	0.000094	0.000086	0.000078					Temp
7	17.0	0.000282	0.000245	0.000221	0.000209	0.000197	0.000179	0.000163				MW	-162.0	-163.7	-166.0
8	17.2	0.000337	0.000293	0.000261	0.000248	0.000235	0.000214	0.000195				16.5	0.000094	0.000091	0.000086
9	17.4	0.000392	0.000342	0.000301	0.000287	0.000274	0.000250	0.000228				16.9	0.000176	0.000170	0.000160
10	17.6	0.000447	0.000390	0.000342	0.000327	0.000312	0.000286	0.000260				17.0	0.000197	0.000189	0.000179
11	17.8	0.000502	0.000438	0.000382	0.000366	0.000351	0.000321	0.000293
12	18.0	0.000557	0.000486	0.000422	0.000405	0.000389	0.000357	0.000325
13	18.2	0.000597	0.000526	0.000460	0.000441	0.000423	0.000385	0.000349
14	18.4	0.000637	0.000566	0.000499	0.000477	0.000456	0.000412	0.000373
15	18.6	0.000677	0.000605	0.000537	0.000513	0.000489	0.000440	0.000397
16	18.8	0.000717	0.000645	0.000575	0.000548	0.000523	0.000467	0.000421
17	19.0	0.000757	0.000685	0.000613	0.000584	0.000556	0.000494	0.000445
18	19.2	0.000800	0.000724	0.000649	0.000619	0.000589	0.000526	0.000474
19	19.4	0.000844	0.000763	0.000685	0.000653	0.000622	0.000558	0.000503
20	19.6	0.000888	0.000803	0.000721	0.000688	0.000655	0.000590	0.000532
21	19.8	0.000932	0.000842	0.000757	0.000722	0.000688	0.000622	0.000561
22	20.0	0.000976	0.000881	0.000793	0.000757	0.000721	0.000654	0.000590
23	25.0	0.001782	0.001691	0.001475	0.001407	0.001339	0.001220	0.001116
24	30.0	0.002238	0.002043	0.001867	0.001790	0.001714	0.001567	0.001435

The formula in M8 and copied across and down is

=BilInterp(M$7, $L8, $A$4:$H$24)

The module that contains the function is at https://app.box.com/s/s0m5gs96jd3stuqa7tpu