hi, need help in how to make interpolation formula based on 2 variable?

[AJIESPEDAS]

hi experts!

i come to a problem again with excel and in need of your expertise to solve my problem.
in given 2 variable for example

12750 lbs with a temperature 31
i need a interpolation that can derive an answer based on image that i give?
is it possible? i have done a single simple interpolation and thats bout it, but this new thing i think it can be done?not so sure..

regards,

ajiespedas

 

[StephenCrump]

You can do a 2-way linear interpolation like this:

	A	B	C	D	E	F	G	H	I	J	K	L
1	Weight	Temp	-5	0	5	10	15		NoRows	4
2	15,000	A	1	2	3	4
3		B	5	6	7	8			Interpolate		Index	Fraction
4		C	9	10	11	12			Measure	B	1
5		D	13	14	15	16			Weight	13,200	13	0.6
6	14,500	A	17	18	19	20			Temp	4	2	0.8
7		B	21	22	23	24			Value	64.4
8		C	25	26	27	28
9		D	29	30	31	32
10	14,000	A	33	34	35	36
11		B	37	38	39	40
12		C	41	42	43	44
13		D	45	46	47	48
14	13,500	A	49	50	51	52
15		B	53	54	55	56
16		C	57	58	59	60
17		D	61	62	63	64
18	13,000	A	65	66	67	68
19		B	69	70	71	72
20		C	73	74	75	76
21		D	77	78	79	80
22	12,500	A
23		B
24		C
25		D
Sheet6

Cell Formulas
Range	Formula
K4	K4	=MATCH(J4,B2:B5,)-1
K5	K5	=MATCH(J5,Weight,-1)
L5	L5	=(J5-INDEX(Weight,K5))/(INDEX(Weight,K5+NoRows)-INDEX(Weight,K5))
K6	K6	=MATCH(J6,Temp,1)
L6	L6	=(J6-INDEX(Temp,K6))/(INDEX(Temp,K6+1)-INDEX(Temp,K6))
J7	J7	=(INDEX(MyTable,K4+K5,K6)*(1-L5)+INDEX(MyTable,K4+K5+NoRows,K6)*L5)*(1-L6)+(INDEX(MyTable,K4+K5,K6+1)*(1-L5)+INDEX(MyTable,K4+K5+NoRows,K6+1)*L5)*L6

Named Ranges
Name	Refers To	Cells
MyTable	=Sheet6!$C$2:$G$25	J7
NoRows	=Sheet6!$J$1	L5, J7
Temp	=Sheet6!$C$1:$G$1	K6:L6
Weight	=Sheet6!$A$2:$A$22	K5:L5

You could build all this into one formula, but it would be a doozy! (Even if you have the latest version of Excel to use the LET function).

 

[AJIESPEDAS]

You can do a 2-way linear interpolation like this:

	A	B	C	D	E	F	G	H	I	J	K	L
1	Weight	Temp	-5	0	5	10	15		NoRows	4
2	15,000	A	1	2	3	4
3		B	5	6	7	8			Interpolate		Index	Fraction
4		C	9	10	11	12			Measure	B	1
5		D	13	14	15	16			Weight	13,200	13	0.6
6	14,500	A	17	18	19	20			Temp	4	2	0.8
7		B	21	22	23	24			Value	64.4
8		C	25	26	27	28
9		D	29	30	31	32
10	14,000	A	33	34	35	36
11		B	37	38	39	40
12		C	41	42	43	44
13		D	45	46	47	48
14	13,500	A	49	50	51	52
15		B	53	54	55	56
16		C	57	58	59	60
17		D	61	62	63	64
18	13,000	A	65	66	67	68
19		B	69	70	71	72
20		C	73	74	75	76
21		D	77	78	79	80
22	12,500	A
23		B
24		C
25		D
Sheet6

Cell Formulas
Range	Formula
K4	K4	=MATCH(J4,B2:B5,)-1
K5	K5	=MATCH(J5,Weight,-1)
L5	L5	=(J5-INDEX(Weight,K5))/(INDEX(Weight,K5+NoRows)-INDEX(Weight,K5))
K6	K6	=MATCH(J6,Temp,1)
L6	L6	=(J6-INDEX(Temp,K6))/(INDEX(Temp,K6+1)-INDEX(Temp,K6))
J7	J7	=(INDEX(MyTable,K4+K5,K6)*(1-L5)+INDEX(MyTable,K4+K5+NoRows,K6)*L5)*(1-L6)+(INDEX(MyTable,K4+K5,K6+1)*(1-L5)+INDEX(MyTable,K4+K5+NoRows,K6+1)*L5)*L6

Named Ranges
Name	Refers To	Cells
MyTable	=Sheet6!$C$2:$G$25	J7
NoRows	=Sheet6!$J$1	L5, J7
Temp	=Sheet6!$C$1:$G$1	K6:L6
Weight	=Sheet6!$A$2:$A$22	K5:L5

You could build all this into one formula, but it would be a doozy! (Even if you have the latest version of Excel to use the LET function).

is there any way i can copy this and have a go at my excel? ill try to manipulate it into my excel..
i have done a simple interpolation, but yours is far complicated..

btw this is my excel which i want to interpolate

toff data test.xlsx

Shared with Dropbox

www.dropbox.com

if you can open it.

 

[StephenCrump]

is there any way i can copy this and have a go at my excel?

Click here, and you can paste the layout and formulae to cell A1 in a blank workbook:

You'll need to create the range names MyTable, NoRows, Temp and Weight

 

[AJIESPEDAS]

Click here, and you can paste the layout and formulae to cell A1 in a blank workbook:

View attachment 111938
You'll need to create the range names MyTable, NoRows, Temp and Weight

I think i got it! i cant thank you enough, this is so powerful and it really helps alot! thanks man!