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

 

[smartin]

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

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.

 

[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

 

[smartin]

Hmm, "area" was not in the original description. Can you show how the
layout?

 

[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

 

[smartin]

Hi, I'm not sure if this is what you need, but give it a look.

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

 

[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