Turning points

[Faretrade4u]

I am trying to evaluate a stream of data and establish both the turning points and the trend of that data . I have some 1000 data samples all in a row and a sample was taken every 1 Second. I can evaluate the turning points when the data peaks or troughs simply enough i.e. data is ......60, 70 , 60 is a peak and 90, 80, 90 is a trough. The problem is when no sample is recorded for the actual peak or trough i.e. .........60,70,70,60.......... or 90,80,80,90. I have been using Excel to try and do this and failed so I have purchased a book on Excel VBA programming and reckon it will take me a few years to find a solution. Manually I can delete the rows that do not change from the previous sample which will cause the data to peak or trough so even I can evaluate it.
Can any body help me do this by helping me write some code in a VBA module.
I have data in the range A1:A1000 but this may have less or more rows than this on another occasion. I want to put a TP next to the data which is a turning point and delete the data that isn’t a TP.
Can you help or do I read that book for the next few years … no no don’t answer that. Any help appreciated.

[Tom Ogilvy]

In B1 put in TP or Delete
In B2 put in the formula

=if(Or(And(A2<A1,A2<A3),And(A2>A1,A2>A3")),"TP","Delete")

then drag fill down the column B.

Select Column B and do Edit=>Copy, then immediately Edit=>Paste Special and
Select Values

Apply an Autofilter to this column (Select the column
Data=>Filter=>Autofilter), then filter on Delete

Select the entire rows for you data and do Edit=>Delete. Only the visible
rows will be deleted. Now do Data=>Filter=>AutoFilter to remove the
autofilter. Delete the top row if appropriate.

--
Regards,
Tom Ogilvy

"Faretrade4u" <Faretrade4u.1ryzia_1121025903.1324@excelforum-nospam.com>
wrote in message
news:Faretrade4u.1ryzia_1121025903.1324@excelforum-nospam.com...
>
> I am trying to evaluate a stream of data and establish both the turning
> points and the trend of that data . I have some 1000 data samples all
> in a row and a sample was taken every 1 Second. I can evaluate the
> turning points when the data peaks or troughs simply enough i.e. data
> is ......60, 70 , 60 is a peak and 90, 80, 90 is a trough. The problem
> is when no sample is recorded for the actual peak or trough i.e.
> ........60,70,70,60.......... or 90,80,80,90. I have been using
> Excel to try and do this and failed so I have purchased a book on Excel
> VBA programming and reckon it will take me a few years to find a
> solution. Manually I can delete the rows that do not change from the
> previous sample which will cause the data to peak or trough so even I
> can evaluate it.
> Can any body help me do this by helping me write some code in a VBA
> module.
> I have data in the range A1:A1000 but this may have less or more rows
> than this on another occasion. I want to put a TP next to the data which
> is a turning point and delete the data that isn't a TP.
> Can you help or do I read that book for the next few years . no no
> don't answer that. Any help appreciated.
>
>
> --
> Faretrade4u
> ------------------------------------------------------------------------
> Faretrade4u's Profile:
http://www.excelforum.com/member.php...o&userid=23651
> View this thread: http://www.excelforum.com/showthread...hreadid=385982
>

[rgeorgerian@carrieraccess.com]

Faretrade4U,

Tom Ogily did nice job on the request. I have question regarding the
peak and trough definitions.

If 60, 70 , 60 is a peak and 90, 80, 90 is a trough; and when no sample
is recorded for the actual peak or trough i.e. ......60,70,70,60.......
or 90,80,80,90, then what is .....60, 61, 60 (is this a peak???) and
90, 89, 90 (is this a trough???).

Thanks.

RichardG

[Faretrade4u]

Many thanks Tom.
I haven't applied your much appreciated solution yet but I can see that it will do the trick. I will be getting back onto my little project soon and will let you know how I get on. Again your help is much appreciated .

Richard
Yes one is peak and the other is trough. As long as the mid point is higher or lower than the before and after points then they are respectively a peak and a trough. Thanks for the interest

Regards

Roger