Can a macro find straight portions of a graph?

  • Thread starter Thread starter tsumanii
  • Start date Start date
T

tsumanii

Hello, I'm not sure if this is possible in excel, but im hoping it is!

Ok I have a very large data set of events occurring at different times
over a 50 year period. When plotted cumulatively I need to identify
the sections of the graph which have a constant gradient(or near
constant) i.e. the straight line parts of the graph.

Firstly, is it possible to write a macro or use functions to do this
with out going through all the data by hand?

Secondly, if this is possible, is there a way to output the records
which do form the straight line portions into a new sheet so i can work
on them?

Hoping someone can help!

Cheers

Sue Mahony
 
Sue,

There is no built-in functionality that can do that. However, you
could use the SLOPE function to calculate the slope for each
increment - groups of two or more data points, and then use some logic
to determine the 'near constant' sections. Once you've added that,
simply filter your data set on that criteria and you'll be able to
isolate the data of interest (use visible cells from your filtered
set) and copy it elsewhere to work with.

HTH,
Bernie
MS Excel MVP
 
There is no built-in functionality that can do that. However, you
could use the SLOPE function to calculate the slope for each
increment - groups of two or more data points, and then use some logic
to determine the 'near constant' sections. Once you've added that,
simply filter your data set on that criteria and you'll be able to
isolate the data of interest (use visible cells from your filtered
set) and copy it elsewhere to work with.
Click to expand...
...

More efficient to calculate second differences adjusted for interval lengths,
and pick the ones with absolute values less than some specified tolerance. If
measurement date were in col A and measured values were in col B, beginning in
row 2, adjusted second differences would begin in C4 as

C4:
=(B4-B3)/(A4-A3)-(B3-B2)/(A3-A2)

But it'd be more efficient still to check the absolute values in these formulas,
so change it to

C4:
=ABS((B4-B3)/(A4-A3)-(B3-B2)/(A3-A2))<TOL

where TOL is the degree of closeness to zero that's acceptable, e.g., 0.001.
Select C4 and fill down as far as needed. The cells evaluating TRUE indicate
that the current row and the two above it represent a 'straight' section.
 
Back
Top