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

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

<colgroup><col><col><col span="7"></colgroup><tbody>
</tbody>

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

<colgroup><col><col><col span="7"></colgroup><tbody>
</tbody>

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

<colgroup><col><col><col span="7"></colgroup><tbody>
</tbody>

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

<colgroup><col><col span="7"></colgroup><tbody>
</tbody>

 

[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).