Pattern Matching

  • Thread starter Thread starter ss_jagger
  • Start date Start date
S

ss_jagger

I'm doing emission testing on a diesel engine capturing Co, Co2, Nox,
No & HC levels with the trace data being captured to an Excel
spreadsheet. The test's are 20 minutes long with varing speeds and
loads. The data is captured at 1 second intervals for a total of 1200
points. What I would like to do is take a small sample of the trace
data, say a 10 second interval, and see if there are any matching
points in the rest of the trace. I'm not concerned if the overal value
is greater or lower, I am more interested in the curve of the trace
matching,

ie. selected sample trace A = 2,4,6,8,6,4,2,2,4 trace B =
12,14,16,18,16,14,12,12,14
B trace value is greater than A, but the curve of the trace is the
same.

Is there a function within Excel that can do this? Thanks
 
the correl() function may do what you want. You will probably need to work
with a statistician as to what values are significant for your process and
sample sizes, (it will be different for each combination
 
I checked out the correl function, usable, but not what I'm looking
for. I need something that will search my 1200 data points to find a
matching curve.
 
=IF(10=SUMPRODUCT(--(($C$1:$C$20-$A1:$A10)=($C$1-$A1))),"Curve Match
Begins", "-")

If your 1200 data points are in A1:A1200, and your 10-point sample is
in C1:C10,
put the above formula in B1 and copy down. "Curve Match Begins" will
flag the start of a complete match, if any.

This looks for an exact match. I.e., if the differences between nine
pairs of points is 8 but the tenth difference is 8.01, this version of
the formula will not indicate a matching curve.

- David
 
If you want to use Correl():

=IF(CORREL($C$1:$C$10, $A1:$A10)>=$G$1,"Correlation","-")


G1 holds your minimum acceptable correlation coefficient (such as .
9999).
All the data in column A, with sample in C1:C10.
This formula in B1 and copy down. "Match" flags the first data point
of the similar curve.

- David
 
Back
Top