1. ## code/formula help (2003)

Hi all,

The attached file has equip sheet where i have an equipment name “ecp” that is installed in a date in column d in field hoor (colun a) and in well 900 column(,in sheet hist i have production history for well 900.
What i want to do is average the well 900 production data from the hist sheet for two scenario before and after the equipment installation date at four time/period intervals, 1 month ,3 months, 6 months and one year, and then output the data as per the format in sheet sample. Please note the sample sheet data is not an extract of the other two sheets.and i want to be able to do it for large number of wells.

2. ## Re: code/formula help (2003)

It would have been helpful if the sample extract matched the dataset so that we could see if our logic matched yours.

In G2 enter the Array formula (confirm with ctrl-shift-enter):
=AVERAGE(IF((HIST!\$A\$2:\$A\$54=\$A2)*(HIST!\$B\$2:\$B\$54 =\$B2)*(HIST!\$C\$2:\$C\$54>=DATE(YEAR(\$D2),MONTH(\$D2)-(\$F2="BEFORE")*\$E2,DAY(\$D2)))*(HIST!\$C\$2:\$C\$54<=DA TE(YEAR(\$D2),MONTH(\$D2)+(\$F2="AFTER")*\$E2,DAY(\$D2) )),HIST!E\$2:E\$54))

Copy G2 to H2:I2. Copy G2:I2 down the columns...

[Note you will get a #DIV/0! error if there is no matching data which could be masked by cond formatting if desired. It will probably be more efficient and be less sluggish with large datasets if the min/max dates for averaging are calculated in an intermediate column instead of within each formula. This is especially important if you don't want to mask the errors but use an IF within each to give some other value when no data exists for that range.

Steve
Steve

3. ## Re: code/formula help (2003)

Thanks Steve,
and very very sorry for not explaining and including an actual extract of data. i was trying to explain the logic by including the period and the scenario column instead i think i confused the logic but let me try to detail my logic,
the logic is based on the equipment installation date which is in this case and based on the month name is may-02 the formula and/or the code will search column c in the hist sheet and for the one month after scenario it will search for the month of jun-02 data, and output the data, if no jun-02 data then it will assume zero average data, and for the three months it will search and average the data for the June, Jul& aug-02 and assumes zero if no month-year match and so on. in the case of before it will be before the month of may-02, that is april-02 for the one month scenario and april, mar&feb for the three months before and so on. in another way for every installation date there will 4 rows of average data before and 4 rows after at the intervals of one month,3 months,6 months and 12 months.

dubdub

4. ## Re: code/formula help (2003)

The question is are my results correct? If not what should they be?

Steve

5. ## Re: code/formula help (2003)

i have attached an actual data sample that reflect what i need. The formula does not output the same result because of different logic.

thanks.

dubdub

6. ## Re: code/formula help (2003)

Those 6 values you posted are the same numbers I get with my formulas. Did you remember to confirm with ctrl-shift-enter?

Also it will not work with your latest attachment since you deleted the "Period" and "Scenario" columns which the formula used to calculate the date ranges...

I have attached the original with the formulas added and cond format to make the errors (no matching data) a light grey and make them less noticeable...
Steve

7. ## Re: code/formula help (2003)

excellent,

i know i may be asking too much, but is there a way to have the output format in sample sheet using the hist and equip sheets because there are too many wells and i did the sample sheet manually to explain the logic.

dubdub

8. ## Re: code/formula help (2003)

I am afraid I don't understand what you mean. The formula uses data from the Hist sheet. The equip sheet is not needed.

Could you elaborate on what you want?

Steve

9. ## Re: code/formula help (2003)

What I am saying is that my input data is the hist and the equip sheets, and what I am asking is if possible to use this data without the pre-existence of the sample sheet to produce the output in the sample sheet.

regards,

dubdub

10. ## Re: code/formula help (2003)

Could you attach a sample worksheet of "EQUIP" that has multiple fields, wells, and Equip so that we understand the layout if we will be extracting from it...

Steve

11. ## Re: code/formula help (2003)

i will when i get to the office. thanks Steve.

dubdub

12. ## Re: code/formula help (2003)

Hi Steve,

Attached is a sample of the data. one other thing the hist sheet data is too huge and i would highly appreciate if you can consider the idea that it will be in one workbook that has multiple sheets and each sheet represents a field and all has the same format similar to that in my previous attachment.

dubdub

13. ## Re: code/formula help (2003)

How is this code?

Note since you do not have the "Equip" column in your "HIST" sheet the numbers for those with different equipment may not calculate correctly. There will also be some duplicates since your equipment list has duplicates entries for items in 1st 4 columns (they are unique in later columns).

Steve

14. ## Re: code/formula help (2003)

hi Steve,

the objective is get the averaged oil,gas and water rates for every well at the 1,3,6,12,months intervals before and after the installation dates of each equip using the well hist sheet. if the well has multiple equip and installed at the same date then it is fine to have the same averaged rates for each equip. if the equip is installed at different dates then at each date there will be a different averaged rates. One last thing the hist data is so huge and it is split into multiple sheet and each sheet has a field name exactly as in the equip sheet, is this going to affect the code from running.

regards,

dubdub

15. ## Re: code/formula help (2003)

I don't see why it would. You will have to run it separate on each hist sheet and each will only average each hist sheet (though you could create a routine to loop through the history sheets). The problem would be ones that are not on one or the other since you will have 2 sets and no good way to combine them

If the datasets are so large that you don't have one sheet of historical data, the array route (even with the calculations turned off) may be very sluggish when it calculates the entire thing at the end.

I would recommend putting it into a different system, a real database, that can average the items together.

The other option would be to create a routine that tried to read through the HIST list only once and create a table of the running averages but that is more work than I have time to invest in this now. Perhaps someone else has the time...

Steve

Page 1 of 2 12 Last

#### Posting Permissions

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