Need to determine where a change in table occurs

[Wox]

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 row’s 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 can’t figure out
how. I have the data formatted in a pivot table, but can’t 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

 

[Wox]

Thanks for the reply Smartin,

Your formula 4 is really close I believe, and much more efficient than what
I was trying to use. The results do need to be separated out by location, but
also by area. This is what was tripping me up, as they are entered in every
other row. I think this is a little closer, but still not quite right:

=IF(OR(E2=E4,A2<>A4),"",(C2-INDEX(C:C,MATCH(1,(E2=$E$1:$E$999)*(A2=$A$1:$A$999),0),1))/365)

Thanks for any additional time you can help out,
Wox

 

[Wox]

Sorry, when I said area, actually meant type, which can be 'a' or 'b' in
alternating rows. The formula currently will look up the first instance of
the grade in the current row, regardless of whether that first instance is
type a or b. I would need the formula to make a check for the earliest grade
of the correct type within the current location.
Hope this helps,
Thanks again for your time,
Wox

 

[Wox]

Thank you Smartin,

That function works great. Just had to modify the blank cell check to E2=E4
or A2<>A4.
Appreciate it,
Wox