Complicated formula assistance needed!

[Pienuts]

Hello, all! I have a project that seems a little out of my league; so I figured who better to ask than the best!

Okay... Explanation time. I need a formula to determine whether or not a well in my spreadsheet is elegible for compensation - just a TRUE or FALSE. Sounds easy enough, no? Wait for it. These are the rules for compensation eligibility:

The first event (Well ID ends in /00) is drilled after 1 Nov 1998
and the date of the associated mineral lease is after 1 May 1996.

OR (now here's where I'm getting stumped)...
if any subsequent event of a non-compensated well is
Drilled after 1 Nov 1998
Associated mineral lease date is after 1 May 1996
and deepened past a certain formation.

Information:
This is intended to be a working spreadsheet, with new wells being added as they are drilled. Currently there are over 16,000 wells for me to work with.

Well IDs in my spreadsheet look like this: ###/##-##-###-##W4/0#
They are all padded with zeroes. The event is the last two digits. 00 is the first, 02 is the second, 03 the third, and so on. No wells in my jurisdiction have more than 06 events. All Well IDs are located in column B.

Drill date is located in column N.

Associated mineral lease date is located in column U.

Formation name is located in column S, and a list of the formations deep enough to be elegible for compensation are on a seperate worksheet, Formation list, A2:A33.

The column I am entering this formula into is column V.

Thanks a lot for your time; I have tryed for days using complex IF and VLOOKUP statements to no avail. My first attempt was a little over 2500 characters! Also I am running 2003, if that makes a difference.

Good luck! If you have any questions, feel free to ask!

*reminder* Only one event of a well can be eligible for compensation, and it should be the earliest event eligible.

-Skip

 

[Steve=True]

can you post a spreadsheet sample so that you can be helped?

www.4shared.com

 

[Pienuts]

Certainly! I've redacted the unnecessary columns for the equation.

http://www.4shared.com/document/RM5NeldN/For_Assistance.html

 

[Steve=True]

Try this in cell v2:

=IF(AND(MID(B2,2,2)="00",U2>DATEVALUE("5/1/1986"),N2>DATEVALUE("11/1/1998")),TRUE,IF(ISERROR(VLOOKUP(S2,'Formation List'!$A$2:$A$33,1,FALSE)),FALSE,IF(AND(U2>DATEVALUE("5/1/1986"),N2>DATEVALUE("11/1/1998")),TRUE,FALSE)))

Updated Formula

 

[Steve=True]

Ooops, forgot to make the lookup an absolute reference

Use this instead:

=IF(AND(MID(B2,2,2)="00",U2>DATEVALUE("5/1/1986"),N2>DATEVALUE("11/1/1998")),TRUE,IF(ISERROR(VLOOKUP(S2,'Formation List'!$A$2:$A$33,1,FALSE)),FALSE,IF(AND(U2>DATEVALUE("5/1/1986"),N2>DATEVALUE("11/1/1998")),TRUE,FALSE)))

 

[Steve=True]

Hi Skip,

In reading your requirements, i missed one.

That you can only compensate an event one time.

I will have to add a checker for that in the formula.

Is it safe to assume that the prior event will always be above the most current event or can it be anywhere in the data set?

 

[Pienuts]

I don't know if that's safe to say. The current list was sorted by the mineral agreement date, so it may not be a safe bet. They can drill today on agreements from 1976. Feel free to sort as you see fit, though...

 

[Pienuts]

So, no dice? I know how you feel...

Thanks for your time anyway!