Wox wrote:
I am trying to look up grade values (column E) in a table that are identified
by a location, type, and date (columns A, B, and C). There are 2 types (a and
b) which are in alternating odd and even columns (type a in even rows, type b
in odd rows) so there are two grades for each date, and each location can
have multiple and different dates associated. Column D is a calculation to
turn dates into years from initial inspection. Grades for a specific location
and type will stay the same for a number of dates, and then increase to n+1
(or sometimes jump to n+2 or 3€¦).

Row1) Location, Type, Date, Calculation, Grade
Row2) location1, a, m/dd/yyyy, calculation1, n
Row3) location1, b, m/dd/yyyy, calculation1, n
up to about 450 rows and 30 different locations

I am interested in determining the length of time it takes to change from
one grade to the next. In order to do this I need to find the row where the
grade changes from n to n+1, and call that rows year calculation to subtract
from it the year where that grade began. This data will go into column F,
with blank wells where there is no change occurring, and the number of years
for change to occur in the well just before the grade change occurred. Is
there a simple way to do this? I imagined a mix of VLOOKUP and ROW functions
or maybe other table lookup functions I am unaware of, but cant figure out
how. I have the data formatted in a pivot table, but cant see a way to get
this kind of information from it. I want to eventually add this data to the
pivot table, where I can see the average amount of time each grade remains
the same before changing, and determine rate of change. I use Excel 2007.

Thanks for your time

Hello

I could not make out whether a change in location is significant for
you. Here are formulas for either requirement.

In all cases, copy/paste in cell F2 and fill down. Assumes rows are
chronological within location.

If a change in location /is not/ significant,

(formula 1 - result in days)
=IF(E2=E3,"",C2-INDEX(C:C,MATCH(E2,E:E,0),1))

(formula 2 - result in fractional years)
=IF(E2=E3,"",(C2-INDEX(C:C,MATCH(E2,E:E,0),1))/365)


If a change in location /is/ significant, we have to add some criteria:

(formula 3* - result in days)
=IF(OR(E2=E3,A2<A3),"",C2-INDEX(C:C,MATCH(1,(E2=$E$1:$E$999)*(A2=$A$1:$A$999 ),0),1))

(formula 4* - result in fractional years)
=IF(OR(E2=E3,A2<A3),"",(C2-INDEX(C:C,MATCH(1,(E2=$E$1:$E$999)*(A2=$A$1:$A$999 ),0),1))/365)

*formulas 3 and 4 must be array entered. After pasting, press
Ctrl+Shift+Enter, do not just press Enter or Tab. Also, I hard-coded a
limit of 999 rows, which should suit your need of ~450, but can be
changed as needed.