Well Inventory Schedule & Reserve Category Lookup

Hi:

I am trying to solve a problem in Excel associated with a Well Inventory / Drilling Schedule. Basically there are two Type Curves (TC1, & TC2). There is an inventory of wells associated with each Type Curve. That inventory within each Type Curve can be categorized by Reserve Categories (PDNP, PUD, PRB). Next there is an assumed priority of drilling this inventory within each Type Curve such as: First: PDNP reserve categories, Next: PUD reserve categories and Finally: PRB reserve categories. The priority of reserve drilling is the same across all type curves (i.e. 1. PDNP wells, 2. PUD wells, 3. PRB wells.)

Then there is an assumed Drill Schedule associated with drilling these inventories across time by Type Curve. I need to construct a table (shown in the attached Excel file) which breaks out the inventory of wells by month, reserve, category and type curve. I have already created the matrix of when the wells are drilled. However I now need to lookup what Reserve Category and Type Curve is associated with each drilled well based on the current inventory available in that Type Curve and based on the priority of drilling each Reserve category.

All inputs are colored Blue. The cells I need to calculate are filled in Yellow color and needs to be dynamic as the well inventory and drill schedule may change. Please ask any questions for clarification. From my perspective, this seems like a rather complex excel formula, and looking forward to the potential solutions for this. Ideally I would like the equation to be calculated within the Yellow cells completely, but if helper cells are needed that definitely makes sense.

Thanks for your help on this.

Are you able to mock-up your expected results in the yellow cells for the provided sample data?

Hi:

I wanted to see if anyone was able to solve this?

I wanted to follow up to see if anyone was able to answer my excel questions above?

Hi Ali:

Thank you, that helps me reorganize the problem a bit. I'm attaching an updated spreadsheet with unique identifiers. However this time I'm skipping the well schedule and going straight to the production profile of each of the wells. I have provided mock up answers for outputs in the yellow cells. I would like to have a dynamic formula that accounts for the drill schedule and the general production profile for each TC (Type Curve) and the risking (production % discount).

Let me know if you have question on the spreadsheet. Thank you for your help on this.

Ali:

Thank you, that is very helpful. That is a great first start. I color coded a section on the example spreadsheet to better show how the intended formula is suppose to work. I have done this only for a portion of the inventory of wells.

Let me know if this is an easy updated to the formula to bring in line with the example I mentioned.

Thank you Alie.

Is there anyone else who would be able to help me with the formula? I believe the color coding is straight forward to show how the formula is working. Basically every time a well is drill it needs to start with the first data point of the production profile from the Type Curve that well is tied to. But each cell also needs to account for the declining production profile of the previous drilled wells. The color coding shows how the wells are tied to production profiles.

Thank you all for your help going through this.

Ok, this is a bit more than a challenge, I can see the pattern, but don't think it is easily formula-able. Will have a closer look at it in the morning.

Try this one in L23, array confirm it before dragging right and down to populate the rest of the table.

Formula:

Please Login or Register  to view this content.

The only thing that the formula does not do is compare the dates in the output table to those in the drill schedule. Both tables need to have the same date columns for the data to match up.
At best, it might be possible to match the first date in the output table to the dates in the drill schedule if it starts earlier, but attempting to check each individual date would not be practical (also, my formula writing capability does have limits)

I notice that kvsrinivasamurthy's formula assumes that the rows of the output table are in identical order to the drill schedule as well as the date columns.

From your original question, I understood that you wanted everything to be cross matched on the assumption that each table could have different headings, or that the headings could be in a different order.

Going on the assumption that both tables will be the same, along with another slight change, my formula can be shortened to

Formula:

Please Login or Register  to view this content.

and still give the correct results.

Looking at the methods and functions in both formulas, I think that each has advantages and disadvantages.

Mine will probably be slower to calculate due to the way the arrays are handled, but kvsrinivasamurthy's formula uses volaile functions, so the potential for increased calculation frequency could make it less desirable.

If you're unsure why using volatile functions can be bad, take an empty cell that is unrelated to the formulas, lets say G20. If you enter something into G20 then all of the formulas that use volatile functions will recalculate, even though they don't need to. The formulas that don't use volatile functions will ignore the change and not recalculate because excel knows that the change will not affect the results.

With complex formulas, where the calculation time can be slow, it might be an advantage to use a slower, non volatile formula and accept one 20 second calculation instead of ten 5 second calculations.