+ Reply to Thread
Results 1 to 4 of 4

Staggered Start Dates - Normalize to Day 1 and Take Average Formula

  1. #1
    Registered User
    Join Date
    09-21-2011
    Location
    Austin, TX
    MS-Off Ver
    Excel 2007
    Posts
    3

    Smile Staggered Start Dates - Normalize to Day 1 and Take Average Formula

    Hello,

    I have a spreadsheet of data of approx 30 rows (wells) by 200 colums (days). Each row represents a producing oil / gas well and the data is in chronological order. Below is simplified example of the data I am working with (I've also included an attachment which is clearer due to the thread automatic formatting). Each numerical value (0,12,8 in the below example) represents a production amount. The first number greater than zero in the row represents the first day of production and the numbers following that represent subsequent production amounts. The wells have staggered first production dates due each well being drilled on different days (i.e. well 1 drilled on 1/2/11, well 2 drilled on 1/3/2011 so on and so forth). What I am trying to do is get the 30-day, 60-day and 90-day average production starting at the first day of production in the least labor intensive way. Is there any formula I can use that will find the first number greater than zero in the row and then return the 30-day, 60-day or 90-day average?

    I tried using a find replace all to replace all the 0's with blanks and then selecting the data, hitting F5, then hitting special, then clicking blanks, then using the ctrl and - function to shift the cells left. This would then give me all the initial production dates for all of the wells in the second column, normalizing the first production date and making it easier for me to find the different averages I needed. This would work if after the first production date there was always a number greater than 0 or some production amount, but unfortunately there are days for which there is no production after the initial production date and by using the methodology I described above I would be overstating the averages due to all 0 production amounts being removed during the find replace all and then deletion of the blank cells.

    Any suggestions at all would be much appreciated. Thank you in advance for your help.

    -Sam


    1/1/2011 1/2/2011 1/3/2011 1/4/2011 1/5/2011 1/6/2011 1/7/2011
    Well 1 0 1 2 5 9 7 9
    Well 2 0 0 6 8 0 0 10
    Well 3 5 6 10 12 8 9 7
    Well 4 0 0 0 0 9 10 8
    Well 5 0 0 8 0 0 4 6
    Attached Files Attached Files

  2. #2
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS365 Family 64-bit
    Posts
    24,532

    Re: Staggered Start Dates - Normalize to Day 1 and Take Average Formula

    Here is a solution that uses a "helper" column. I believe this can be done without it but it makes the formulas more manageable.

    This solution uses an array formula, which is enclosed in brackets in the formula box. To save such a formula, press CTRL+SHIFT+ENTER instead of just ENTER (you can't just type in the brackets).
    Jeff
    | | |會 |會 |會 |會 | |:| | |會 |會
    Read the rules
    Use code tags to [code]enclose your code![/code]

  3. #3
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Staggered Start Dates - Normalize to Day 1 and Take Average Formula

    Hi,

    That's an extremely neat solution.

    The only query I have, and it's because the OP is not quite clear enough, relates to any zeros after the first production date.

    Should any zeros after the first production day be included in the average calculation or not? Your solution assumes that's so. I just wonder in view of the OPs comments about removing zeros whether that applies to all zeros or just zeros before the well starts production.

    Regards
    Richard Buttrey

    RIP - d. 06/10/2022

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

  4. #4
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS365 Family 64-bit
    Posts
    24,532

    Re: Staggered Start Dates - Normalize to Day 1 and Take Average Formula

    Hmm. Thanks for the good words, and a good point.

    If we do a little detective work on the OP we find this passage:
    Quote Originally Posted by austin123 View Post
    This would work if after the first production date there was always a number greater than 0 or some production amount, but unfortunately there are days for which there is no production after the initial production date and by using the methodology I described above I would be overstating the averages due to all 0 production amounts being removed during the find replace all and then deletion of the blank cells.
    This implies that removing zeroes from the production dates would lower the desired average. I didn't think about it when I developed the solution, but hopefully what I did is consistent with what's needed. Maybe austin123 will come back to tell us....

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1