Count Consecutive Values

Good day everyone,

I'm trying to use this formula to count consecutive values but somehow it doesn't work properly.
Does anyone see what's going wrong here or is there a better formula to this?


Thank you for any help offered,


Roberto Lucesi

Please Login or Register  to view this content.

If you're happy to use a UDF perhaps:

Please Login or Register  to view this content.

the above, stored in a Module in a Macro enabled file, could be called from cell A1 using:

Please Login or Register  to view this content.

using your example data this would return 2
(note it's geared to look in upwards direction only - could be altered)

You have the option of passing TRUE or FALSE as a 2nd parameter where FALSE would stipulate that blanks were not to be ignored (default is TRUE - ignore blanks)

It depends on what you think are "consecutive values". By the transpose you now look at
how many are consecutive to the first +
how many are consecutive to the second +
.....
how many are consecutive to the last.

With 122 you have
0,0,0 + 1,0,0 + 1,0,0 (actually twice the 1-2 combination)
With 123 you have
0,0,0 + 1,0,0 + 0,1,0 (1-2 and 2-3 combination)

And this:

=SUM(--(A2:A5-OFFSET(A2:A5,1,0)=-1))

CSE (array function)

Roberto, I am not sure my UDF actually does what you want...

What is you're looking to do exactly...

Are you looking to iterate each number in the data set and see how many other numbers within the dataset are within 1 of the value being iterated and sum the results of all iterations

or

Are you looking to cycle the values and see how many values follow the prior value ?

Perhaps a bigger sample may help ?

Still not sure I follow... wouldn't the answer be 6 ?

Please Login or Register  to view this content.

I say 6 based on:

1: 2 instances of 2
2: 1 instance of 1
2: 1 instance of 1
15: 1 instance of 16
16: 1 instance of 15

Perhaps I'm missing the underlying point...

Sorry, I forgot to adapt from UK to Dutch format - you need to change the comma to semi-colon:

Please Login or Register  to view this content.