Multiple Interpolations

[tonii4516]

Can someone explain to me the easiest way to implement mulitple interpolations, I have two unknowns that depend on temperatures given and cfm given. Total I am given 3 different temperatures and one cfm which makes it complicated to find a way to make a formula to find this unknown values when the temperatures aren't the exact ones given in the table.
It sounds confusing and it because it is. Any help will be greatly appreciated.

 

[Eric W]

Can you show an example of your table, and a sample input and expected output? You can use the HTML Maker in my signature to show a screen print of your spreadsheet, or just use the table tool in the Advanced editor (although that can be more tedious).

 

[tonii4516]

 

[SpillerBD]

Interpolation or iteration?
Circular references may seem like a non-no, but Excel can do them. If your calculations seems impossible without cicurlar reference, proceed with it and turn on the iterative calcaulations through the Options.
https://support.office.com/en-us/ar...recision-73fc7dac-91cf-4d36-86e8-67124f6bcce4

 

[Eric W]

Sorry, you can't upload a picture directly to the forum. You can upload it to a file-sharing service, then include the link. However, even that is not recommended, since no can can copy/paste your sample data to work with. If you just include a picture, people will have to manually type in your data to work with, and many people won't take the time. That's why the other 2 methods I suggested are preferred.

 

[tonii4516]

Hello, I have optimize the question in a way I can provide an example. I copy the image to a file in google drive. Hopefully, it will work if not i'll find another way to share the table.

How to get an interpolation for multiple variables. CFM, Outdoor Temperature, Wet Temperature, and Dry Temperature are given. Trying to get an interpolation for Total Capacity and Sensible Ratio. So an example would be, what is the Total Capacity and Sensible ratio when CFM=1100, Wet Temperature is 65F, Dry Temperature is 78F, and Outdoor Temperature is 100F.

https://drive.google.com/file/d/1bMHU0VTt_TJbF6sCR_626zCeU2UYimUR/view?usp=sharing

 

[Eric W]

I'm not sure what to tell you. First, your image is a picture, which can't be copied into a test workbook. You're not going to find many people who will spend the time to type all that in. Second, I've done 1-d interpolation, and 2-d interpolation. 4-d interpolation in theory should just merely be an extension of that. However, in looking at your table, the functions of the various dimensions are clearly not linear. That might be tolerable in a 2-d table, but I suspect that any formula I could come up with would likely be wildly inaccurate in some places. I'll think about it a bit more and let you know if I come up with something.

 

[tonii4516]

https://drive.google.com/open?id=1l-EeW-ux4uE2QOQgC9dbjFtlRMwGm2M-

Here is the link for a table that can be open in google sheets.

I hope that helps.

Thank you for your time.

 

[Eric W]

I must be a bit of a masochist, because I figured it out. I hope that's your exact table, since if it's not, adapting it will be tricky. But here goes:

Excel 2012

	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V
1	Wet Temperature		Outdoor Temperature
2			85°F					95°F					105°F					115°F
3			Total Capacity		Sensible ratio			Total Capacity		Sensible ratio			Total Capacity		Sensible ratio			Total Capacity		Sensible Ratio
4
5					Dry Temperature					Dry Temperature					Dry Temperature					Dry Temperature
6		cfm	kBtuh		75°F	80°F	85°F	kBtuh		75°F	80°F	85°F	kBtuh		75°F	80°F	85°F	kBtuh		75°F	80°F	85°F
7	63°F	960	36.5		0.69	0.85	1	34.3		0.7	0.87	1	32		0.71	0.89	1	29.5		0.72	0.92	1
8		1200	38.6		0.75	0.94	1	36.3		0.77	0.97	1	33.9		0.78	1	1	31.5		0.8	1	1
9		1440	40.4		0.82	1	1	38.2		0.83	1	1	36		0.86	1	1	33.6		0.89	1	1
10	67°F	960	39		0.54	0.67	0.81	36.8		0.54	0.68	0.83	34.4		0.54	0.69	0.85	31.8		0.54	0.7	0.88
11		1200	41.1		0.57	0.73	0.9	38.8		0.58	0.74	0.93	36.3		0.58	0.76	0.96	33.6		0.59	0.78	0.99
12		1440	42.7		0.61	0.79	0.99	40.2		0.62	0.81	1	37.5		0.62	0.83	1	34.9		0.63	0.86	1
13	71°F	960	41.4		0.4	0.53	0.65	39.1		0.4	0.53	0.65	36.7		0.38	0.53	0.67	34.1		0.38	0.53	0.68
14		1200	43.7		0.42	0.56	0.71	41.3		0.41	0.57	0.72	38.7		0.41	0.57	0.74	36.1		0.4	0.58	0.75
15		1440	45.3		0.43	0.6	0.77	42.8		0.43	0.61	0.79	40.1		0.43	0.61	0.81	37.3		0.42	0.63	0.83
16
17
18	CFM	1100		Total Capacity	35.5125
19	Wet Temp	65
20	Dry Temp	78		Sensible ratio	0.761083
21	Outdoor Temp	100
22
23				Total Capacity		Sensible ratio
24
25						Dry Temperature						Dry Temp
26				kBtuh		75	80	85				78					kBtuh	Sensible Ratio
27				33.15		0.705	0.88	1				0.81					34.375	0.7185
28	Outdoor Temps			35.1		0.775	0.985	1		Dry Temp		0.901		Wet Temp			36.325	0.7915		cfm
29	85	95	5	37.1		0.845	1	1		75	0	0.938		63	63	0	37.975	0.839		960	0
30	95	105	10	35.6		0.54	0.685	0.84		80	1	0.627		67	67	3				1200	1
31	105			37.55		0.58	0.75	0.945		85		0.682		71						1440
32	115			38.85		0.62	0.82	1				0.74
33				37.9		0.39	0.53	0.66				0.474
34				40		0.41	0.57	0.73				0.506
35				41.45		0.43	0.61	0.8				0.538

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

Sheet2​

Worksheet Formulas Cell Formula E18 =FORECAST(B18,OFFSET(Q27:Q28,U29,0),OFFSET(T29:T30,U29,0)) E20 =FORECAST(B18,OFFSET(R27:R28,U29,0),OFFSET(T29:T30,U29,0)) B29 =VLOOKUP(B21,A29:A32,1) C29 =(MATCH(B21,A29:A32)-1)*5 B30 =INDEX(A29:A32,MATCH(B29,A29:A32,0)+1) C30 =C29+5 D27 =(OFFSET(C7,0,$C$30)-OFFSET(C7,0,$C$29))/($B$30-$B$29)*($B$21-$B$29)+OFFSET(C7,0,$C$29) K29 =MATCH(B20,J29:J31)-1 K30 =K29+1 L26 =B20 L27 =FORECAST($B$20,OFFSET(F27:G27,0,$K$29),OFFSET($F$26:$G$26,0,$K$29)) O29 =LOOKUP(B19,N29:N31) P29 =(MATCH(O29,N29:N31,0)-1)*3 O30 =INDEX(N29:N31,MATCH(O29,N29:N31,0)+1) P30 =(MATCH(O30,N29:N31,0)-1)*3 Q27 =(OFFSET(D27,$P$30,0)-OFFSET(D27,$P$29,0))/($O$30-$O$29)*($B$19-$O$29)+OFFSET(D27,$P$29,0) R27 =(OFFSET(L27,$P$30,0)-OFFSET(L27,$P$29,0))/($O$30-$O$29)*($B$19-$O$29)+OFFSET(L27,$P$29,0) U29 =MATCH(B18,T29:T31)-1 U30 =U29+1 <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

The D27 formula should be copied to D27:D35 and to F27:H35. The L27 formula should be copied to L28:L35, Q27 copied through Q29, R27 though R29. All other formulas are individual, all other cells are constants copied from other places in your table.

Each section is an individual interpolation. The D26:H35 section interpolates the outdoor temperature table from the 4 subtables above. L27:L35 uses the dry temperature to interpolate the sensible ratio from F27:H35. Q27:Q29 interpolates capacity from wet temperature, R27:R29 interpolates sensible ratio from wet temperature. Finally E18 and E20 interpolate ratio and capacity from Q27:R29 and cfm.

So put your values in B18:B21, and the results will be in E18 and E20. If you use a value at one of the upper extremes, like a wet temperature of 71 degrees, use 70.9 instead or the formulas will fail.

Hope this helps! If you send me your email address in a PM, I'll send you a copy of the workbook.

 

[tonii4516]

Thank you Erick,

My email is emilianoayala4516@gmail.com