# Thread: Find Upper and lower boundary numbers. calculate average rate between them

1. ## 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)

Thanks,
sample.xlsm

2. What value should be in row 199 and what is the logic?

Steve

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

4. 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,

5. 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

6. 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 ctrl-shift-enter):
=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 ctrl-shift-enter):
=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

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•