Calculating rate of change over variable time interval

[Guest]

This is the data I have...

Column A: Date (every 10 minutes)
Column B: Permeability (this is the parameter whose rate of change I'm
trying to calculate) - records every 10 minutes
Column C: Time since last cleaning (from a different spreadsheet that
records every 30 minutes)

What I need to do...

Permeability declines over time and a cleaning is performed every few days.
But, the exact cleaning interval is always different. I need to calculate
the rate of change between the pre-clean permeability and the previous
cleaning's post-clean permeability. The problem is that there is a lot of
data in between. It's easy to recognize when a cleaning has occurred (Time
Since Last Clean equals zero), but how can I get Excel to recognize and pick
out the permeability right before the previous cleaning and also calculate
the time interval between cleaning events in order to determine the fouling
rate?

Any help would be greatly appreciated.

 

[Guest]

Assuming that this set of data covers several/many cleaning intervals, this
is much better managed in a database. The reason for saying that is that
databases are designed to work with SETS of data, and each cleaning interval
is a set.

Since you've got all this in Excel, though, I'd suggest that you use
Data->Filter->Advanced filter to copy the rows with a "Time Since Last Clean"
= zero to an empty space on your worksheet

Once you have isolated this set of rows, use the Match function to find the
row number of each date (datetime) of cleaning in the first column of the
entire data set. From there, use the Index() function to find the next
previous datetime and pull out its permeability reading.

Hope this makes sense