CODEWITHSUNDEEP
excelexcel-formula
asteqt
asteqt

Reputation: 3

Count series where next values of cells are greater

I'd like to count series where minimum 3 next values of cells are greater. For example we have this numbers:

1
5
6
7
4
2
5
8
9
10

The result of the formula should be 2 ([1,5,6,7] and [2,5,8,9,10]). I'd also like to find the length of the longest series

Upvotes: 0

Views: 59

Answers (1)

JvdV
JvdV

Reputation: 75870

Well, you could try the following:

In B1:

=MAX(IF(FREQUENCY(IF(A1:A10<A2:A11,ROW(A1:A10)),IF(A1:A10<A2:A11,"",ROW(A1:A10)))>=2,FREQUENCY(IF(A1:A10<A2:A11,ROW(A1:A10)),IF(A1:A10<A2:A11,"",ROW(A1:A10)))+1,""))

In B2:

=SUM(IF((IF(FREQUENCY(IF(A1:A10<A2:A11,ROW(A1:A10)),IF(A1:A10<A2:A11,"",ROW(A1:A10)))>=2,FREQUENCY(IF(A1:A10<A2:A11,ROW(A1:A10)),IF(A1:A10<A2:A11,"",ROW(A1:A10)))+1,""))<>"",1,0))

Note: Enter both through CtrlShiftEnter

enter image description here

Upvotes: 3

Related Questions