4 Linear Interpolations... I think.

[jwell1515]

Here are 4 tables of iterated values for bottom hole pressures in CO2 injection wells. I’d like to be able to input a Surface Pressure (Psurf), TD (total depth), average fluid temperature Tavg, and CO2% and have excel go find the best approximation of the bottom hole pressure from the tables. Basically it needs to do 4 interpolations. I am totally stumped. Any help at all would be much appreciated. I've pasted the four data sets the best I can.

Thanks

John

[TABLE="width: 503"]
<colgroup><col><col><col span="7"></colgroup><tbody>[TR]
[TD][/TD]
[TD]CO2[/TD]
[TD="align: right"]100[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]TD=4925[/TD]
[TD]Tavg=70[/TD]
[TD]Tavg=75[/TD]
[TD]Tavg=80[/TD]
[TD]Tavg=85[/TD]
[TD]Tavg=90[/TD]
[TD]Tavg=95[/TD]
[TD]Tavg=100[/TD]
[/TR]
[TR]
[TD]Psurf[/TD]
[TD]1800[/TD]
[TD]3672[/TD]
[TD]3635[/TD]
[TD]3595[/TD]
[TD]3553[/TD]
[TD]3509[/TD]
[TD]3461[/TD]
[TD]3409[/TD]
[/TR]
[TR]
[TD]Psurf[/TD]
[TD]1850[/TD]
[TD]3729[/TD]
[TD]3692[/TD]
[TD]3654[/TD]
[TD]3612[/TD]
[TD]3569[/TD]
[TD]3522[/TD]
[TD="align: right"]3471[/TD]
[/TR]
[TR]
[TD]Psurf[/TD]
[TD]1900[/TD]
[TD]3786[/TD]
[TD]3750[/TD]
[TD]3712[/TD]
[TD]3671[/TD]
[TD]3629[/TD]
[TD]3583[/TD]
[TD="align: right"]3533[/TD]
[/TR]
[TR]
[TD]Psurf[/TD]
[TD]1950[/TD]
[TD]3843[/TD]
[TD]3807[/TD]
[TD]3771[/TD]
[TD]3731[/TD]
[TD]3690[/TD]
[TD]3645[/TD]
[TD="align: right"]3596[/TD]
[/TR]
[TR]
[TD]Psurf[/TD]
[TD]2000[/TD]
[TD]3899[/TD]
[TD]3865[/TD]
[TD]3828[/TD]
[TD]3788[/TD]
[TD]3748[/TD]
[TD]3705[/TD]
[TD="align: right"]3658[/TD]
[/TR]
[TR]
[TD]Psurf[/TD]
[TD]2050[/TD]
[TD]3955[/TD]
[TD]3921[/TD]
[TD]3886[/TD]
[TD]3847[/TD]
[TD]3807[/TD]
[TD]3765[/TD]
[TD="align: right"]3720[/TD]
[/TR]
[TR]
[TD]Psurf[/TD]
[TD]2100[/TD]
[TD]4011[/TD]
[TD]3978[/TD]
[TD]3943[/TD]
[TD]3905[/TD]
[TD]3866[/TD]
[TD]3825[/TD]
[TD="align: right"]3782[/TD]
[/TR]
[TR]
[TD]Psurf[/TD]
[TD]2150[/TD]
[TD]4067[/TD]
[TD]4035[/TD]
[TD]4001[/TD]
[TD]3964[/TD]
[TD]3926[/TD]
[TD]3887[/TD]
[TD="align: right"]3845[/TD]
[/TR]
[TR]
[TD]Psurf[/TD]
[TD]2200[/TD]
[TD]4123[/TD]
[TD]4091[/TD]
[TD]4058[/TD]
[TD]4022[/TD]
[TD]3985[/TD]
[TD]3947[/TD]
[TD="align: right"]3907[/TD]
[/TR]
[TR]
[TD]Psurf[/TD]
[TD]2250[/TD]
[TD]4179[/TD]
[TD]4147[/TD]
[TD]4115[/TD]
[TD]4080[/TD]
[TD]4044[/TD]
[TD]4007[/TD]
[TD="align: right"]3979[/TD]
[/TR]
[TR]
[TD]Psurf[/TD]
[TD]2300[/TD]
[TD]4234[/TD]
[TD]4203[/TD]
[TD]4171[/TD]
[TD]4137[/TD]
[TD]4102[/TD]
[TD]4066[/TD]
[TD]4029
[/TD]
[/TR]
</tbody>[/TABLE]


[TABLE="width: 499"]
<colgroup><col><col><col span="7"></colgroup><tbody>[TR]
[TD][/TD]
[TD]CO2[/TD]
[TD="align: right"]98[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Depth[/TD]
[TD]TD=4925[/TD]
[TD]Tavg=70[/TD]
[TD]Tavg=75[/TD]
[TD]Tavg=80[/TD]
[TD]Tavg=85[/TD]
[TD]Tavg=90[/TD]
[TD]Tavg=95[/TD]
[TD]Tavg=100[/TD]
[/TR]
[TR]
[TD]Psurf[/TD]
[TD]1800[/TD]
[TD]3647[/TD]
[TD]3614[/TD]
[TD]3578[/TD]
[TD]3542[/TD]
[TD]3503[/TD]
[TD]3461[/TD]
[TD]3417[/TD]
[/TR]
[TR]
[TD]Psurf[/TD]
[TD]1850[/TD]
[TD]3703[/TD]
[TD]3670[/TD]
[TD]3634[/TD]
[TD]3598[/TD]
[TD]3559[/TD]
[TD="align: right"]3518[/TD]
[TD="align: right"]3474[/TD]
[/TR]
[TR]
[TD]Psurf[/TD]
[TD]1900[/TD]
[TD]3759[/TD]
[TD]3727[/TD]
[TD]3691[/TD]
[TD]3656[/TD]
[TD]3618[/TD]
[TD="align: right"]3577[/TD]
[TD="align: right"]3534[/TD]
[/TR]
[TR]
[TD]Psurf[/TD]
[TD]1950[/TD]
[TD]3815[/TD]
[TD]3783[/TD]
[TD]3748[/TD]
[TD]3713[/TD]
[TD]3675[/TD]
[TD="align: right"]3635[/TD]
[TD="align: right"]3594[/TD]
[/TR]
[TR]
[TD]Psurf[/TD]
[TD]2000[/TD]
[TD]3871[/TD]
[TD]3840[/TD]
[TD]3805[/TD]
[TD]3771[/TD]
[TD]3734[/TD]
[TD="align: right"]3695[/TD]
[TD="align: right"]3655[/TD]
[/TR]
[TR]
[TD]Psurf[/TD]
[TD]2050[/TD]
[TD]3927[/TD]
[TD]3896[/TD]
[TD]3862[/TD]
[TD]3828[/TD]
[TD]3792[/TD]
[TD="align: right"]3754[/TD]
[TD="align: right"]3715[/TD]
[/TR]
[TR]
[TD]Psurf[/TD]
[TD]2100[/TD]
[TD]3982[/TD]
[TD]3952[/TD]
[TD]3919[/TD]
[TD]3886[/TD]
[TD]3850[/TD]
[TD="align: right"]3813[/TD]
[TD="align: right"]3775[/TD]
[/TR]
[TR]
[TD]Psurf[/TD]
[TD]2150[/TD]
[TD]4038[/TD]
[TD]4009[/TD]
[TD]3976[/TD]
[TD]3944[/TD]
[TD]3909[/TD]
[TD="align: right"]3873[/TD]
[TD="align: right"]3836[/TD]
[/TR]
[TR]
[TD]Psurf[/TD]
[TD]2200[/TD]
[TD]4093[/TD]
[TD]4064[/TD]
[TD]4032[/TD]
[TD]4000[/TD]
[TD]3966[/TD]
[TD="align: right"]3931[/TD]
[TD="align: right"]3895[/TD]
[/TR]
[TR]
[TD]Psurf[/TD]
[TD]2250[/TD]
[TD]4147[/TD]
[TD]4119[/TD]
[TD]4088[/TD]
[TD]4057[/TD]
[TD]4024[/TD]
[TD="align: right"]3990[/TD]
[TD="align: right"]3955[/TD]
[/TR]
[TR]
[TD]Psurf[/TD]
[TD]2300[/TD]
[TD]4203[/TD]
[TD]4175[/TD]
[TD]4145[/TD]
[TD]4115[/TD]
[TD]4083[/TD]
[TD]4051[/TD]
[TD]4017[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 503"]
<colgroup><col><col><col span="7"></colgroup><tbody>[TR]
[TD][/TD]
[TD]CO2[/TD]
[TD]100[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]TD=4975[/TD]
[TD]Tavg=70[/TD]
[TD]Tavg=75[/TD]
[TD]Tavg=80[/TD]
[TD]Tavg=85[/TD]
[TD]Tavg=90[/TD]
[TD]Tavg=95[/TD]
[TD]Tavg=100[/TD]
[/TR]
[TR]
[TD]Psurf[/TD]
[TD]1800[/TD]
[TD]3691[/TD]
[TD]3653[/TD]
[TD]3613[/TD]
[TD]3571[/TD]
[TD]3527[/TD]
[TD]3479[/TD]
[TD]3426[/TD]
[/TR]
[TR]
[TD]Psurf[/TD]
[TD]1850[/TD]
[TD]3747[/TD]
[TD]3710[/TD]
[TD]3672[/TD]
[TD]3630[/TD]
[TD]3587[/TD]
[TD]3540[/TD]
[TD="align: right"]3489[/TD]
[/TR]
[TR]
[TD]Psurf[/TD]
[TD]1900[/TD]
[TD]3804[/TD]
[TD]3768[/TD]
[TD]3730[/TD]
[TD]3689[/TD]
[TD]3647[/TD]
[TD]3601[/TD]
[TD="align: right"]3551[/TD]
[/TR]
[TR]
[TD]Psurf[/TD]
[TD]1950[/TD]
[TD]3861[/TD]
[TD]3825[/TD]
[TD]3789[/TD]
[TD]3749[/TD]
[TD]3708[/TD]
[TD]3663[/TD]
[TD="align: right"]3614[/TD]
[/TR]
[TR]
[TD]Psurf[/TD]
[TD]2000[/TD]
[TD]3917[/TD]
[TD]3883[/TD]
[TD]3846[/TD]
[TD]3806[/TD]
[TD]3766[/TD]
[TD]3723[/TD]
[TD="align: right"]3676[/TD]
[/TR]
[TR]
[TD]Psurf[/TD]
[TD]2050[/TD]
[TD]3973[/TD]
[TD]3939[/TD]
[TD]3904[/TD]
[TD]3865[/TD]
[TD]3825[/TD]
[TD]3783[/TD]
[TD="align: right"]3738[/TD]
[/TR]
[TR]
[TD]Psurf[/TD]
[TD]2100[/TD]
[TD]4029[/TD]
[TD]3996[/TD]
[TD]3961[/TD]
[TD]3923[/TD]
[TD]3884[/TD]
[TD]3843[/TD]
[TD="align: right"]3800[/TD]
[/TR]
[TR]
[TD]Psurf[/TD]
[TD]2150[/TD]
[TD]4085[/TD]
[TD]4053[/TD]
[TD]4019[/TD]
[TD]3982[/TD]
[TD]3944[/TD]
[TD]3905[/TD]
[TD="align: right"]3863[/TD]
[/TR]
[TR]
[TD]Psurf[/TD]
[TD]2200[/TD]
[TD]4141[/TD]
[TD]4109[/TD]
[TD]4076[/TD]
[TD]4040[/TD]
[TD]4003[/TD]
[TD]3965[/TD]
[TD="align: right"]3925[/TD]
[/TR]
[TR]
[TD]Psurf[/TD]
[TD]2250[/TD]
[TD]4197[/TD]
[TD]4165[/TD]
[TD]4133[/TD]
[TD]4098[/TD]
[TD]4062[/TD]
[TD]4025[/TD]
[TD="align: right"]3997[/TD]
[/TR]
[TR]
[TD]Psurf[/TD]
[TD]2300[/TD]
[TD]4254[/TD]
[TD]4221[/TD]
[TD]4189[/TD]
[TD]4155[/TD]
[TD]4120[/TD]
[TD]4084[/TD]
[TD]4046[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 446"]
<colgroup><col><col span="7"></colgroup><tbody>[TR]
[TD]CO2[/TD]
[TD]98[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]TD=4975[/TD]
[TD]Tavg=70[/TD]
[TD]Tavg=75[/TD]
[TD]Tavg=80[/TD]
[TD]Tavg=85[/TD]
[TD]Tavg=90[/TD]
[TD]Tavg=95[/TD]
[TD]Tavg=100[/TD]
[/TR]
[TR]
[TD]1800[/TD]
[TD]3666[/TD]
[TD]3632[/TD]
[TD]3596[/TD]
[TD]3560[/TD]
[TD]3521[/TD]
[TD]3479[/TD]
[TD]3434[/TD]
[/TR]
[TR]
[TD]1850[/TD]
[TD]3721[/TD]
[TD]3688[/TD]
[TD]3652[/TD]
[TD]3616[/TD]
[TD]3577[/TD]
[TD]3536[/TD]
[TD="align: right"]3492[/TD]
[/TR]
[TR]
[TD]1900[/TD]
[TD]3777[/TD]
[TD]3745[/TD]
[TD]3709[/TD]
[TD]3674[/TD]
[TD]3636[/TD]
[TD]3595[/TD]
[TD="align: right"]3552[/TD]
[/TR]
[TR]
[TD]1950[/TD]
[TD]3833[/TD]
[TD]3801[/TD]
[TD]3766[/TD]
[TD]3731[/TD]
[TD]3693[/TD]
[TD]3653[/TD]
[TD="align: right"]3612[/TD]
[/TR]
[TR]
[TD]2000[/TD]
[TD]3889[/TD]
[TD]3858[/TD]
[TD]3823[/TD]
[TD]3789[/TD]
[TD]3752[/TD]
[TD]3713[/TD]
[TD="align: right"]3673[/TD]
[/TR]
[TR]
[TD]2050[/TD]
[TD]3945[/TD]
[TD]3914[/TD]
[TD]3880[/TD]
[TD]3846[/TD]
[TD]3810[/TD]
[TD]3772[/TD]
[TD="align: right"]3733[/TD]
[/TR]
[TR]
[TD]2100[/TD]
[TD]4000[/TD]
[TD]3970[/TD]
[TD]3937[/TD]
[TD]3904[/TD]
[TD]3868[/TD]
[TD]3831[/TD]
[TD="align: right"]3793[/TD]
[/TR]
[TR]
[TD]2150[/TD]
[TD]4056[/TD]
[TD]4027[/TD]
[TD]3994[/TD]
[TD]3962[/TD]
[TD]3927[/TD]
[TD]3891[/TD]
[TD="align: right"]3854[/TD]
[/TR]
[TR]
[TD]2200[/TD]
[TD]4111[/TD]
[TD]4082[/TD]
[TD]4050[/TD]
[TD]4018[/TD]
[TD]3984[/TD]
[TD]3949[/TD]
[TD="align: right"]3913[/TD]
[/TR]
[TR]
[TD]2250[/TD]
[TD]4165[/TD]
[TD]4137[/TD]
[TD]4106[/TD]
[TD]4075[/TD]
[TD]4042[/TD]
[TD]4008[/TD]
[TD="align: right"]3973[/TD]
[/TR]
[TR]
[TD]2300[/TD]
[TD]4222[/TD]
[TD]4193[/TD]
[TD]4163[/TD]
[TD]4133[/TD]
[TD]4101[/TD]
[TD]4069[/TD]
[TD]4035[/TD]
[/TR]
</tbody>[/TABLE]

 

[Sal Paradise]

Here are 4 tables of iterated values for bottom hole pressures in CO2 injection wells. I’d like to be able to input a Surface Pressure (Psurf), TD (total depth), average fluid temperature Tavg, and CO2% and have excel go find the best approximation of the bottom hole pressure from the tables. Basically it needs to do 4 interpolations. I am totally stumped. Any help at all would be much appreciated. I've pasted the four data sets the best I can.

What you want is multiple linear regression:
LINEST - Excel - Office.com

However, when I do it out for your data, it doesn't come up with a reasonable answer. So there's something wrong. Basically, a vast majority of the final value seems to be determined by the PSurf more than anything else. If you had more values to fill in (rather than just 2 values for CO2 and TD) then we may get a better result.

You could read through this article which will give you more info:
Trendline coefficients

 

[b.downey]

COuld you provide an example of the input Values... and the result, from the table that you are looking to achieve

 

[Sal Paradise]

I understand what he's looking for, I just can't figure out the math.

For instance, he is looking for the bottom pressure at 99 CO2 with TD of 4925, Psurf of 1800, And Tavg of 80.

He has two values in the table:
100 CO2, TD 4925, Psurf 1800, Tavg 80 = 3595
98 CO2, TD 4925, Psurf 1800, Tavg 80 = 3578

So he wants a value in between those two figures (3595 and 3578)

Or he has two values:
98 CO2, TD 4975, Psurf 1800, Tavg 80 = 3596
98 CO2, TD 4925, Psurf 1800, Tavg 80 = 3578

He wants to find the same for TD 4950, which again should be in between the values.

If it's just one variable to extrapolate, it isn't so bad. But he has 4:
1) CO2
2) TD
3) Psurf
4) Tavg

And TREND() won't solve for 4 variables.

So he has to do linear regression.

If you run standard multiple linear regression on his data, you get the following results:

Excel 2010
	A	B	C	D	E	F	G	H	I	J	K
1	CO2	TD	Psurf	TD	Bottom Hole Pressure
2	98	4925	1800	70	3647		-7.12468	1.163649	0.36026	8.980519	-609.579
3	98	4925	1850	70	3703		0.125217	0.007919	0.050087	1.25217	277.8755
4	98	4925	1900	70	3759		0.987992	21.97549	#N/A	#N/A	#N/A
5	98	4925	1950	70	3815		6232.72	303	#N/A	#N/A	#N/A
6	98	4925	2000	70	3871		12039677	146325.5	#N/A	#N/A	#N/A
7	98	4925	2050	70	3927
8	98	4925	2100	70	3982
9	98	4925	2150	70	4038
10	98	4925	2200	70	4093		CO2	TD	Psurf	TD
11	98	4925	2250	70	4147		98	4925	1800	70
12	98	4925	2300	70	4203
13	98	4975	1800	70	3666		Result:	5700.28
14	98	4975	1850	70	3721
15	98	4975	1900	70	3777
16	98	4975	1950	70	3833
17	98	4975	2000	70	3889
18	98	4975	2050	70	3945
19	98	4975	2100	70	4000
20	98	4975	2150	70	4056
21	98	4975	2200	70	4111
22	98	4975	2250	70	4165
23	98	4975	2300	70	4222
24	100	4925	1800	70	3672
25	100	4925	1850	70	3729
26	100	4925	1900	70	3786
27	100	4925	1950	70	3843
28	100	4925	2000	70	3899
29	100	4925	2050	70	3955
30	100	4925	2100	70	4011
31	100	4925	2150	70	4067
32	100	4925	2200	70	4123
33	100	4925	2250	70	4179
34	100	4925	2300	70	4234
35	100	4975	1800	70	3691
36	100	4975	1850	70	3747
37	100	4975	1900	70	3804
38	100	4975	1950	70	3861
39	100	4975	2000	70	3917
40	100	4975	2050	70	3973
41	100	4975	2100	70	4029
42	100	4975	2150	70	4085
43	100	4975	2200	70	4141
44	100	4975	2250	70	4197
45	100	4975	2300	70	4254
46	98	4925	1800	75	3614
47	98	4925	1850	75	3670
48	98	4925	1900	75	3727
49	98	4925	1950	75	3783
50	98	4925	2000	75	3840
51	98	4925	2050	75	3896
52	98	4925	2100	75	3952
53	98	4925	2150	75	4009
54	98	4925	2200	75	4064
55	98	4925	2250	75	4119
56	98	4925	2300	75	4175
57	98	4975	1800	75	3632
58	98	4975	1850	75	3688
59	98	4975	1900	75	3745
60	98	4975	1950	75	3801
61	98	4975	2000	75	3858
62	98	4975	2050	75	3914
63	98	4975	2100	75	3970
64	98	4975	2150	75	4027
65	98	4975	2200	75	4082
66	98	4975	2250	75	4137
67	98	4975	2300	75	4193
68	100	4925	1800	75	3635
69	100	4925	1850	75	3692
70	100	4925	1900	75	3750
71	100	4925	1950	75	3807
72	100	4925	2000	75	3865
73	100	4925	2050	75	3921
74	100	4925	2100	75	3978
75	100	4925	2150	75	4035
76	100	4925	2200	75	4091
77	100	4925	2250	75	4147
78	100	4925	2300	75	4203
79	100	4975	1800	75	3653
80	100	4975	1850	75	3710
81	100	4975	1900	75	3768
82	100	4975	1950	75	3825
83	100	4975	2000	75	3883
84	100	4975	2050	75	3939
85	100	4975	2100	75	3996
86	100	4975	2150	75	4053
87	100	4975	2200	75	4109
88	100	4975	2250	75	4165
89	100	4975	2300	75	4221
90	98	4925	1800	80	3578
91	98	4925	1850	80	3634
92	98	4925	1900	80	3691
93	98	4925	1950	80	3748
94	98	4925	2000	80	3805
95	98	4925	2050	80	3862
96	98	4925	2100	80	3919
97	98	4925	2150	80	3976
98	98	4925	2200	80	4032
99	98	4925	2250	80	4088
100	98	4925	2300	80	4145
Sheet4

Cell Formulas
Range	Formula
H13	=G11*G2+H11*H2+I2*I11+J2*J11+K2
G2:K6	{=LINEST(E2:E309,A2:D309,TRUE,TRUE)}
Press CTRL+SHIFT+ENTER to enter array formulas.

(I can only paste 100 lines, the table continues for all other values too).

Obviously 5700 is totally off, since the range of values in the table is 3409-4254, and the value for the particular combination I put in is 3647 (first value in the table). But I don't know why it's off, because multiple linear regression is an art not a science and I don't understand the data or relationship (it looks like 99.9% of the value should be modified by the Psurf alone).