Graphing cyclic data

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Rick - 25 Mar 2008 16:53 GMT

I have a single column of cyclic generated data. The cycles are over time but
are not always consistent . Though it is very easy to generate a graph of the
cycles, I am trying to pull out ONLY the minimums and maximums of each cycle
for graphing, each its own data series. The number of cycles can range from
10 to 10,000 and and may have as few as 10 points per cycle to as many as
1000 points per cycle so data points can range 100 points to 10,000,000
points from which I need to pull all the minimums and all the maximums
(representing the peaks and valleys of each cycle.

I struggling with a way to do this and I would greatly appreciate any help
anyone can be.

THANKS!!

Signature

Rick

Reply to this Message

Gary''s Student - 25 Mar 2008 17:30 GMT

The trick is to be able to locate and pull aside the maximums and minimums.
Let's say your data is in column A. In B1 enter 0. In B2 enter:

=(A2<A1)*(A2<A3)+(A2>A1)*(A2>A3)*2 and copy down. Here is an example of
what you might see:

1    0
2    0
3    0
4    0
5    2
4    0
3    0
2    0
1    1
2    0
3    0
4    0
5    0
6    2
5    0
4    0
3    0
2    0
1    0
0    1
2    0
4    0
6    2
Minimums have the value 1 in column B. Maximums have the value 2 in column B.

Just use an AutoFilter on column B and copy/paste the visible cells to an
area you can chart from.

Signature

Gary''s Student - gsnu200775

> I have a single column of cyclic generated data. The cycles are over time but
> are not always consistent . Though it is very easy to generate a graph of the
[quoted text clipped - 9 lines]
>
> THANKS!!

Reply to this Message

Rick - 25 Mar 2008 18:16 GMT

Thanks Gary"s Student!

It almost works, probably because of something I forgot to mention; all the
minimum values are negative numbers.
Rick

> The trick is to be able to locate and pull aside the maximums and minimums.
> Let's say your data is in column A. In B1 enter 0. In B2 enter:
[quoted text clipped - 43 lines]
> >
> > THANKS!!

Reply to this Message

Rick - 25 Mar 2008 18:29 GMT

Also, my data isn;t a smooth sine wave type of transition but more of a saw
tooth where the positive maximums and the negative minimums are adjacent to
each other. Here's a little more than 2 cycles example of my data...

0.34421
0.74345
5.256
16.113
27.384
44.445
48.433
-34.23
-16.487
-7.7462
-0.22392
3.464
17.815
29.043
51.321
50.773
-32.12
-17.169

Signature

Rick

> Thanks Gary"s Student!
>
[quoted text clipped - 49 lines]
> > >
> > > THANKS!!

Reply to this Message

Jon Peltier - 25 Mar 2008 19:13 GMT

When I use the formula proposed by Gary''s Student with the data you
provided, the formulas correctly pick out the min and max values.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______

> Also, my data isn;t a smooth sine wave type of transition but more of a
> saw
[quoted text clipped - 87 lines]
>> > >
>> > > THANKS!!

Reply to this Message

Gary''s Student - 25 Mar 2008 19:22 GMT

Much Better!!

In B1 put 0
In B2 enter:

=IF(B1=1,2,IF((A2>0)*(A3<0),1,0)) and copy down to see:

0.34421    0
0.74345    0
5.256    0
16.113    0
27.384    0
44.445    0
48.433    1
-34.23    2
-16.487    0
-7.7462    0
-0.22392    0
3.464    0
17.815    0
29.043    0
51.321    0
50.773    1
-32.12    2
-17.169    0

The ones are the maximums and the twos are the minimums.

Signature

Gary''s Student - gsnu200775

> Also, my data isn;t a smooth sine wave type of transition but more of a saw
> tooth where the positive maximums and the negative minimums are adjacent to
[quoted text clipped - 72 lines]
> > > >
> > > > THANKS!!

Reply to this Message

Rick - 25 Mar 2008 19:53 GMT

THANKS VERY MUCH TO BOTH OF YOU GUYS!! YOU ARE LIFE SAVERS!!

Signature

Rick

> Much Better!!
>
[quoted text clipped - 100 lines]
> > > > >
> > > > > THANKS!!

Reply to this Message