Multiple Interpolation

[eggyjla]

I'm attempting multiple interpolations for the table below. This is merely a sub-set of the entire table. I also have a UDF for interpolation that accepts (5) variables [X1, Y1, X2, Y2, X] and return the interpolated value [Y]. I'm uncertain how the best method of returning an interpolated value from a 2D table given three criteria. R[1], R[2], and C[1] are the criteria. How best to return a value from the table given the criteria R[1]=75; R[2]=60; C[1]=2.5

Lighting Zonal Cavity.xls
	D	E	F	G	H	I	J	K	L
4		80	80	80	80	70	70	70	70
5		70	50	30	10	70	50	30	10
6	1	1.092	1.082	1.075	1.068	1.077	1.070	1.064	1.059
7	2	1.079	1.066	1.055	1.047	1.068	1.057	1.048	1.039
8	3	1.070	1.054	1.042	1.033	1.061	1.048	1.037	1.028
9	4	1.062	1.045	1.033	1.024	1.055	1.040	1.029	1.021
10	5	1.056	1.038	1.026	1.018	1.050	1.034	1.024	1.015
11	6	1.052	1.033	1.021	1.014	1.047	1.030	1.020	1.012
12	7	1.047	1.029	1.018	1.011	1.043	1.026	1.017	1.009
13	8	1.044	1.026	1.015	1.009	1.040	1.024	1.015	1.007
14	9	1.040	1.024	1.014	1.007	1.037	1.022	1.014	1.006
15	10	1.037	1.022	1.012	1.006	1.034	1.020	1.012	1.005
Sheet4

 

[Oaktree]

Normalize your data so that it has four columns:

(1) X value
(2) Y value
(3) instance
(4) data value

Then, consider running a regression model (either with tools --> data analysis --> regression or by using the LINEST function) to predict (4) based on (1) and (2).

 

[Kaps_mr2]

put a row 3 in which is a concanenation of 4&5 :-

a3= a4&" "&a5 (space for clarity)

index($e$6:$z$25,match(p30,$e$3:$z$3,0),match(q30,$d$6:$d$25,0))

where e6:z25 is your data range
p30 is the cell that chooses which row you require
q30 is the concatenation of the two indexes that choose which column you require.

Thanks

Kaps

 

[eggyjla]

Oaktree:
Could you explain a bit more...please. Not certain I understand how to consolidate into X and Y values or 2 columns.

 

[Oaktree]

Sure.

You want your data layout to be something like this so that you can use the Regression tool:

Book7
	A	B	C	D
14	R1	R2	C1	Value
15	80	70	1	1.092
16	80	50	1	1.082
17	80	30	1	1.075
18	80	10	1	1.068
19	70	70	1	1.077
20	70	50	1	1.07
21	70	30	1	1.064
22	70	10	1	1.059
23	80	70	2	1.079
24	80	50	2	1.066
25	80	30	2	1.055
26	80	10	2	1.047
27	70	70	2	1.068
28	70	50	2	1.057
29	70	30	2	1.048
30	70	10	2	1.039
Sheet1

 

[eggyjla]

Oaktree:

Got the spreadsheet working with LINEST() func. However for X values that are in the table, the calculated Y value from the LINEST() output does not return the number in the table. I calculated the %error for each Y value in the table to each calculated Y value and the %errors were large ranging from -400%-180%. Does the normalized data table need to be in any specific sorted order?

Book3.xls
	H	I	J	K	L
1073	-5.26796	0.358395	0.56863	4.077044	#N/A
1074	0.129381	0.009957	0.007661	0.603622	#N/A
1075	0.927381	6.232463	#N/A	#N/A	#N/A
1076	4542.021	1067	#N/A	#N/A	#N/A
1077	529285.2	41446.11	#N/A	#N/A	#N/A
Sheet1

 

[Oaktree]

The order would not matter.

It's hard to tell without seeing your data and without knowing anything about the logic of the interaction of the variables, but note the pvalues of your coefficients. With a pvalue of 0.603 (cell K1074), your intercept has a confidence level that's less than 40%. With a pvalue of .1293, your C1 coefficient (note that the order of the variables is reversed... cell H1073 corresponds to column J of your inputs) has a confidence level that's less than 90%.

Thus, it's not terribly surprising that the predictive results are not great. Maybe it would make logical sense to force the intercept of your model to be 0?