Sequence of Up->Down->Up-> repeat until....

[ibmy]

Hi,

Is this possible to detect this kind of sequence in 250k-750k row data.

1) Count start at least the sequence of up-down
2) Stop the count if next value is not in squence of, increasing(up)-decreasing(down)-increasing(up)-decreasing(down),.... , example:

i- up-down->down
ii- up-down->same value as previous
iii- up-down-up->up
iv- up-down-up->same value as previous

9.13 - 17.xlsb
	G	H
1
2	1.18186
3	1.18187
4	1.18189
5	1.18190
6	1.18189	1
7	1.18192	1
8	1.18190	1
9	1.18192	1
10	1.18194
11	1.18185	1
12	1.18184
13	1.18183
14	1.17554
15	1.17555
16	1.17553	1
17	1.17554	1
18	1.17554
19	1.17555
20	1.17553	1
21	1.17554	1
22	1.17554
23	1.17555
24	1.17554	1
25	1.17556	1
26	1.17557
27	1.17553	1
28	1.17554	1
29	1.17553	1
30	1.17552
31	1.17553
32	1.17553
Sheet1

 

[JEC]

Try this
Paste this in H4 and pull down

=IF(OR(AND(A3>A2,A3>A4),AND(A3<A2,A3<A4)),1,"")

 

[ibmy]

Thanks @JEC
Some work, some do not work on short sequence but mostly it work on counting long sequence.
My interests on data is long sequence so that all matter.

Anyway, here some do not work base on the example above,
Cell C15 and C31

9.13 - 17.xlsb
	A	B	C
1
2	1.18186
3	1.18187
4	1.18189
5	1.1819
6	1.18189	1	1
7	1.18192	1	1
8	1.1819	1	1
9	1.18192	1	1
10	1.18194
11	1.18185	1	1
12	1.18184
13	1.18183
14	1.17554
15	1.17555		1
16	1.17553	1	1
17	1.17554	1	1
18	1.17554
19	1.17555
20	1.17553	1	1
21	1.17554	1	1
22	1.17554
23	1.17555
24	1.17554	1	1
25	1.17556	1	1
26	1.17557
27	1.17553	1	1
28	1.17554	1	1
29	1.17553	1	1
30	1.17552
31	1.17553		1
32	1.17553
Sheet1

Cell Formulas
Range	Formula
C4:C32	C4	=IF(OR(AND(A3>A2,A3>A4),AND(A3<A2,A3<A4)),1,"")