Results 1 to 6 of 6

20121218, 16:30 #1
 Join Date
 Aug 2012
 Posts
 3
 Thanks
 0
 Thanked 0 Times in 0 Posts
Find Upper and lower boundary numbers. calculate average rate between them
For each Group I need to find Cum Produced fluid after 720hrs, 1440hrs, 2160hrs and 3600 hrs.
Column A is the group’s unique name for each well. Column B is the time in months the Well has produced and Column C is the # of hours that Column B month is has produced for. Since my interest is in 720, 1440, 2160 and 3600hrs I need to search though each well and calculate the average Producing rate in the 4 time periods. So basically once I have the average rate bbls/day I can divide it by 24hrs to become bbls/hr and multiple it by my interested time periods (720,1440,2160,3600 hours)
Does anyone know a better formula to use then:
=IF(A2<>A1,IF(AND(C2<720,C3>720),AVERAGE(D23)/24*720,0),0)
This work ok until the upper and lower hrs with in the interested hours range happens earlier than expected or later. I highlighted the row 199 where the abnormality happens.
Further once the correct 720, 1440, 2160, 3600hrs are found Id like the to be a value for each time the groups unique name is repeated. (Columns I – L)
I have attached a spreadsheet.
Thanks,
sample.xlsm

20121218, 17:28 #2
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
What value should be in row 199 and what is the logic?
Steve

20121218, 17:52 #3
 Join Date
 Aug 2012
 Posts
 3
 Thanks
 0
 Thanked 0 Times in 0 Posts
If you paste these formulas into E199, F199, G199 , H199 respectively you will get the values
617.5569946 1378.050495 1729.168552 2077.619453
Col E
=AVERAGE(D199201)/24*720
Col F
=IF(A199<>A198,IF(AND(C201<1440,C202>1440),(AVERAG E(D201202)/24*720)+E199,0),0)
Col g
=IF(A199<>A198,IF(AND(C202<2160,C204>2160),(AVERAG E(D202204)/24*720)+F199,0),0)
Col h
=IF(A199<>A198,IF(AND(C205<3600,C206>3600),(AVERAG E(D204206)/24*720)+G199,0),0)

20121218, 20:00 #4
 Join Date
 Aug 2012
 Posts
 3
 Thanks
 0
 Thanked 0 Times in 0 Posts
More on the logic.
What I am try to estimate is the rate of flow in a time period. I have 4 time periods. 720, 1440, 2160 3600 hrs (30 , 60, 90, 120 days)
a) For each unique group once the well has produced for 720 hr what was the average rate. There may be 3 rows (more or less) of hours before the 720 hrs is reached. Sometimes its reached on the first row. Once the time has been reached I need to average the rate colume over the # of rows it took to get to 720 hrs. The logic is then to move on to the next rows looking for a value greater then 720 and less then 1440. The data will be over 1440hr most of the time so a formula would just take the next value as being 1440 even if its way over. Then again take the number of rows it took to get to 1440 from the last find of 720 and average the rate column over those rows.
Hoefully this kind helps you help me?
Thanks,

20121219, 02:38 #5
 Join Date
 Oct 2012
 Posts
 55
 Thanks
 1
 Thanked 10 Times in 10 Posts
Hi 7milemiracle
I am not totally sure what you mean, but see the attached. Columns N:Q have the averages per well number within the criteria you have in E1:H1. If it is not what you require perhaps you can adapt it!
Kevin

20121221, 12:28 #6
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
Not sure if all your numbers are correct. This gets most of them and seems to do your logicIn E2 enter the array formula (confirm with ctrlshiftenter):
=IF(OR($A2=$A1),0,AVERAGE(OFFSET($D$1,MATCH(E$1,IF ($A$2:$A$340=$A2,$C$2:$C$340)),0,2,1))/24*720)
In F2 enter the array formula (confirm with ctrlshiftenter):
=IF(OR($A2=$A1),0,AVERAGE(OFFSET($D$1,MATCH(F$1,IF ($A$2:$A$340=$A2,$C$2:$C$340)),0,2,1))/24*720+E2)
Copy F2 to G2:H2
Copy E2:H2 to E3:H340
This matches all your numbers except row E199:H199 (which was the one giving you all zeroes). It also does not match the zeroes you got in H47 and H122. Those zeroes come stem from the same problem as all of row 199
Steve