Last week I set readers the challenge which can be found here.

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!*

=MODE.MULT(SMALL(A1:D5,ROW(A1:A20)),IF(COUNTIF(A1:D5,A1:D5+1)+COUNTIF(A1:D5,A1:D5-1),A1:D5))

*Great stuff, Lori! Can’t believe we all missed that subtle, yet crucial, re-arrangement!)*

And that solution is (at 103 characters excluding the equals sign):

=MODE.MULT(IFERROR(SMALL(IF(COUNTIF(A1:D5,A1:D5+1)+COUNTIF(A1:D5,A1:D5-1),A1:D5),ROW(A1:A20))*{1,1},""))

**How does it work?**

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.:

=MODE.MULT(IF(MMULT(COUNTIF(A1:D5,SMALL(A1:D5,ROW(A1:A20))-{1,-1}),{1;1}),{1,1}*SMALL(A1:D5,ROW(A1:A20))))

(The variation:

MODE.MULT(IF(MMULT(COUNTIF(A1:D5,SMALL(A1:D5,ROW(A1:A20))-{1,-1}),{1;1}),SMALL(A1:D5,ROW(A1:A20))),A1:D5)

is of precisely the same length.)

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.

Hence, using the data originally posted, this part:

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

resolves as:

{0,0,1,0;0,0,0,0;1,1,0,1;1,0,1,1;1,0,0,0}+{0,1,1,0;0,0,0,1;0,1,1,1;1,0,0,0;1,0,0,0}

which is:

{0,1,2,0;0,0,0,1;1,2,1,2;2,0,1,1;2,0,0,0}

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:

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

which is:

IF({0,1,2,0;0,0,0,1;1,2,1,2;2,0,1,1;2,0,0,0},A1:D5)

i.e.:

{FALSE,17,14,FALSE;FALSE,FALSE,FALSE,37;12,15,3,2;13,FALSE,1,36;16,FALSE,FALSE,FALSE}

We now have our required values. However, there still remains the two tasks of sorting them in ascending order and of removing any non-numerical entries.

The first of these is quite straightforward. We simply use SMALL, such that:

SMALL(IF(COUNTIF(A1:D5,A1:D5+1)+COUNTIF(A1:D5,A1:D5-1),A1:D5),ROW(A1:A20))

which is here:

SMALL({FALSE,17,14,FALSE;FALSE,FALSE,FALSE,37;12,15,3,2;13,FALSE,1,36;16,FALSE,FALSE,FALSE},ROW(A1:A20))

i.e.:

SMALL({FALSE,17,14,FALSE;FALSE,FALSE,FALSE,37;12,15,3,2;13,FALSE,1,36;16,FALSE,FALSE,FALSE},{1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17;18;19;20})

i.e.:

{1;2;3;12;13;14;15;16;17;36;37;#NUM!;#NUM!;#NUM!;#NUM!;#NUM!;#NUM!;#NUM!;#NUM!;#NUM!}

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:

SMALL(IF(COUNTIF(A1:D5,A1:D5+1)+COUNTIF(A1:D5,A1:D5-1),A1:D5),ROW(A1:A20))*{1,1}

which is:

{1;2;3;12;13;14;15;16;17;36;37;#NUM!;#NUM!;#NUM!;#NUM!;#NUM!;#NUM!;#NUM!;#NUM!;#NUM!}*{1,1}

resolves as:

{1,1;2,2;3,3;12,12;13,13;14,14;15,15;16,16;17,17;36,36;37,37;#NUM!,#NUM!;#NUM!,#NUM!;#NUM!,#NUM!;#NUM!,#NUM!;#NUM!,#NUM!;#NUM!,#NUM!;#NUM!,#NUM!;#NUM!,#NUM!;#NUM!,#NUM!}

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.

And so:

IFERROR(SMALL(IF(COUNTIF(A1:D5,A1:D5+1)+COUNTIF(A1:D5,A1:D5-1),A1:D5),ROW(A1:A20))*{1,1},"")

which is:

IFERROR({1,1;2,2;3,3;12,12;13,13;14,14;15,15;16,16;17,17;36,36;37,37;#NUM!,#NUM!;#NUM!,#NUM!;#NUM!,#NUM!;#NUM!,#NUM!;#NUM!,#NUM!;#NUM!,#NUM!;#NUM!,#NUM!;#NUM!,#NUM!;#NUM!,#NUM!},"")

gives:

{1,1;2,2;3,3;12,12;13,13;14,14;15,15;16,16;17,17;36,36;37,37;"","";"","";"","";"","";"","";"","";"","";"","";"",""}

Finally, then, we pass this array to MODE.MULT, such that:

MODE.MULT(IFERROR(SMALL(IF(COUNTIF(A1:D5,A1:D5+1)+COUNTIF(A1:D5,A1:D5-1),A1:D5),ROW(A1:A20))*{1,1},""))

which is:

MODE.MULT({1,1;2,2;3,3;12,12;13,13;14,14;15,15;16,16;17,17;36,36;37,37;"","";"","";"","";"","";"","";"","";"","";"","";"",""})

gives:

{1;2;3;12;13;14;15;16;17;36;37}

as required.

Thanks again to all who contributed. Another challenge to follow shortly. Watch this space!

Great works! Lori beats all, really 🙂

Just would like to suggest more universal (but, of cource, not the shortest) solution:

instead ROW(A1:A20) as pecalculated range lets make dynamic range:

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.

Hi,

I followed your explanation, step by step and each time I use evaluate (F9), I am able to see the results exactly the way you have shown.

Good learning for me and a great explanation.

Thank you XOR LX

Regards,

PrasadDN.

PS: btw, what is your name? no doubt XORLX sounds great!

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.

-Alex

Pingback: #Excel Super Links #22 – shared by David Hager | Excel For You