A good response to this one, leading to a solution for which, in the end, most people who responded can take some credit.

Snakehips started the ball rolling with a nice logical construction involving “OR”ing two separate COUNTIFs; John Jairo V then shaved off several characters from this solution; this was then further refined by Elias; and, finally, after several attempts at constructing a solution using FREQUENCY, Alex Groberman took the COUNTIF set-up and wrapped it in that most wonderful of functions – MODE.MULT – to give us our winner.

So congratulations to all of the above, though perhaps in particular to Alex for his many contributions and for providing the finalised version!

(Post-script: Lori has just contacted me with the following slight variation, which comes in at just 91 characters!

It might be worth first pointing out that, rather surprisingly perhaps, the attempt to shortcut this construction by evaluating the two COUNTIF constructions simultaneously – and also doing away with the need for an IFERROR clause – ends up being slightly longer than the above, at 105 characters, i.e.:

Of course, if this wasn’t a shortest formula challenge then this might be the preferable choice here.

Anyway, back to our winner, then. Since we are looking to return an array comprising values from our range which form part of a consecutive sequence of at least two entries from that range, one way to do this is to check, for each of the values in our range, whether either of the values which are one greater or one less than that value are also found within the range.

Effectively, then, we need to look at the results of the two constructions:

COUNTIF(A1:D5,A1:D5+1)

and

COUNTIF(A1:D5,A1:D5-1)

And, since we require, for each of our values, that only one of these two returns be non-zero, we can form the equivalent of an OR construction by simply adding together the above.

from which, just to clarify, an entry of 0 represents the fact that neither the value 1 greater nor the value 1 less than that being queried is found within the array. Likewise, an entry of 1 tells us that either the value 1 greater or the value 1 less than that being queried is found within the array (but not both). And, finally, a value of 2 means that both the value 1 greater and the value 1 less than that being queried are found within the array.

Clearly it is the non-zero entries in which we are interested, and so we form:

Now, the reason for my calling MODE.MULT “wonderful” in the opening paragraph is because, whereas we might normally seek to reduce this array by constructing an appropriate array to pass to SMALL as its k parameter (which is precisely what Snakehips and John did in their solutions), we can often – as here – do without going to such lengths. And all thanks to MODE.MULT (and to Lori again for having re-introduced many of us to the possibilities inherent in this function).

I would first like to point out that the technique used here, in which an array of values can be reduced in such a fashion, is only appropriate if the elements which we wish to return appear equally frequently within the array being processed. Furthermore, that frequency must be at least twice, since MODE.MULT does not deem any values to be worthy of return if no value occurs more than once.

Hence the reason for Alex’s prior multiplication with the array {1,1}, since, when we take the product of our array resulting from the SMALL construction (a 20-row-by-1-column array) with the array {1,1} (a 1-row-by-2-column array), we will effectively generate a a 20-row-by-2-column array, the entries in each of its 2 columns being identical.

Reader not too familiar with array operations should take note of this technique, which here means that:

and, as can be seen, we have succeeding in simply doubling the number of each of our entries. As such, we can now safely pass to MODE.MULT. First, however, we need to remove those errors. As wonderful as it is, MODE.MULT – like the vast majority of functions – isn’t happy to process arrays containing such values.

Others had done all the hard work – this was just a play with Alex’s solution which was the true challenge winner. Since this is a shortest formula i had started by noticing one character could be saved by using {"",""} instead of *{1,1} before spotting the suggested rearrangement.

Wow Lori, what a fantastic line of thinking! It was really all you, since you gave me the idea of mode.mult in the first place. It’s great how many applications that little function has when you start thinking about it.