CODEWITHSUNDEEP
excel-formula
SoftwareTester
SoftwareTester

Reputation: 1090

Count the number of sequences in a range

I would like to count the number of sequences in a dataset. A new sequence is started whenever a the value changes from zero to positive or from positive to zero.

The data represent mm rain one entry for every hour of the day. If it starts raining a new sequence starts, if it stops raining a new sequence starts as well. On some days it keeps raining (1 sequence of rain, 0 sequences of dry). On others days it will be dry (0 sequence of rain, 1 sequence of dry). The other days will have X sequences of rain and Y sequences of dry (X and Y differ 0 or 1).

Example: Range (N1:N25) contains

RH
0
0
0
0
0
0
1
3
9
2
0
0
0
0
0
0
2
0
0
0
0
0
0
0

Using Matrix formulas I can calculate The longest sequence of non-zero's, being 4, by

=MAX(FREQUENCY(IF($N$2:$N$25>0,ROW($N$2:$N$25)),IF($N$2:$N$25<=0,ROW($N$2:$N$25)))

but i cannot calculate the number of sequences of non-zero's, being 2, nor the total number of sequences, being 5.

How can I do that?

I looked for the answer on Stackoverflow but couldn't find it

Upvotes: 0

Views: 246

Answers (1)

Scott Craner
Scott Craner

Reputation: 152450

To get the count of non 0 groupings:

=SUMPRODUCT(--(FREQUENCY(IF($N$2:$N$25<>0,ROW($N$2:$N$25)),IF($N$2:$N$25=0,ROW($N$2:$N$25)))>0))

To get the count of 0 groupings:

=SUMPRODUCT(--(FREQUENCY(IF($N$2:$N$25=0,ROW($N$2:$N$25)),IF($N$2:$N$25<>0,ROW($N$2:$N$25)))>0))

then you can add them together.

Upvotes: 2

Related Questions