Results 1 to 6 of 6
  1. #1
    New Lounger
    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

  2. #2
    WS Lounge VIP sdckapr's Avatar
    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

  3. #3
    New Lounger
    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)

  4. #4
    New Lounger
    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,

  5. #5
    Star Lounger
    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

  6. #6
    WS Lounge VIP sdckapr's Avatar
    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 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
  •