# Shortest Formula Challenge #4: Consecutive Integers 33

The challenge this week is as follows: given the range A1:D5, in which each of the entries is an integer and is unique within that range, a single formula to generate an array consisting of all values which form part of a consecutive sequence of at least two entries from that range. The elements within this array are also to be returned in an order from smallest to largest.

For the example below:

one possible answer would be (as highlighted):

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

I say “one possible answer” since, provided that the values are correct and in order from smallest to largest, the dimensions of the array are arbitrary. Hence:

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

is an equally valid solution here.

Since the required solution is an array, an output into an actual worksheet cell is not required for this challenge. Readers not familiar with the Evaluate Formula tool are advised to research this feature. I would also remind solvers that piecemeal “evaluation” of formulas by repeated pressing of F9 within the formula bar does not always produce correct evaluations.

Of course, visualization of the required array will not be possible unless some external, coercing function is applied, though readers should note that any such additional function will not form part of a final solution. (My personal preference for corroborating array outputs via Evaluate Formula is to wrap a construction in an arbitrary function; SUM being my usual choice.)

Note that this is a shortest formula challenge, which means that readers should attempt to find not only a correct solution to the problem but also one which has the least number of characters as possible.

Any ranges must include both a row and column reference: A:D or 1:5, for example, are not acceptable. Named Ranges are also not permitted.

Solution next week. Best of luck!

1. Here is my best effort to kick things off.

`=SMALL(IF((COUNTIF(A1:D5,(A1:D5-1))=0)*(COUNTIF(A1:D5,(A1:D5+1))=0),"X",A1:D5),ROW(INDIRECT("A1:A"&20-SUM((COUNTIF(A1:D5,(A1:D5-1))=0)*(COUNTIF(A1:D5,(A1:D5+1))=0)))))`

Given that this is a shortest formula challenge I would imagine that it is something of an embarrassing monster.

I await something sleeker from the great and the good!

2. @Snakehips

And a very good starter indeed! Congratulations!

I make that 166 characters (without the equals sign). Let’s wait and see what others bring to the table!

Many thanks and good to see you here again!

Cheers

3. John Jairo V says:

Hi!

With @Snakehips approach… could be shorter like this:

`=SMALL(IF(COUNTIF(A1:D5,A1:D5-1)+COUNTIF(A1:D5,A1:D5+1),A1:D5),ROW(INDIRECT("1:"&SUM(--(COUNTIF(A1:D5,A1:D5-1)+COUNTIF(A1:D5,A1:D5+1)>0)))))`

139 characters without the equals sign. I’m looking for another shorter solution.

Blessings!

4. ChrisBM says:
`=SMALL(IF(OR(A1:D5-A1:D5=-1,A1:D5-A1:D5=1),"",A1:D5),ROW(INDIRECT("1:"&20-OR(A1:D5-A1:D5=-1,A1:D5-A1:D5=1))))`
5. Alex Groberman says:

Here’s my crude attempt:

`=MODE.MULT(IF(FREQUENCY(A1:D5,ROW(A1:A50))*(FREQUENCY(A1:D5,ROW(A2:A51))+FREQUENCY(A1:D5,ROW(A1:A50)-1))>0,FREQUENCY(A1:D5,ROW(A1:A50))*ROW(A1:A51)*{1,1}))`
6. Alex Groberman says:

Slight revision to save characters!

`=MODE.MULT(IF(FREQUENCY(A1:D5,ROW(A1:A50))*(FREQUENCY(A1:D5,ROW(A2:A51))+FREQUENCY(A1:D5,ROW(A1:A50)-1)),FREQUENCY(A1:D5,ROW(A1:A50))*ROW(A1:A51)*{1,1}))`
7. @John Jairo V

Hi! And, yes, I’m happy with that adaptation of Snakehips’ solution. Good stuff!

Cheers

8. @ChrisBM

Hi, and thanks a lot for your offering!

Not sure if my comment editor played about with your solution, though, as it is, I’m not getting correct results.

For starters, won’t this part:

`A1:D5-A1:D5`

always result in an array of nothing but zeroes?

Also, you might want to use the Evaluate Formula tool to take a look at what happens when you try to apply an OR construction like that within an array formula.

Regards

9. @Alex Groberman

Thanks a lot, Alex!

Unfortunately I’m not getting correct results for all scenarios with those two solutions either.

What do they give if, for example, the range contains the number 1, though not the number 2?

Regards

10. Alex Groberman says:

Thanks for debugging it!

Fixed:

`=MODE.MULT(IF(FREQUENCY(A1:D5+1,ROW(A1:A50))*(FREQUENCY(A1:D5+2,ROW(A1:A50))+FREQUENCY(A1:D5,ROW(A1:A50))),FREQUENCY(A1:D5+1,ROW(A1:A50))*ROW(A1:A51)-{1,1}))`

By adding 1 to A1:D5 and then subtracting 1 at the end, it fixes the special case where a lone “1” in the list is treated as consecutive when it isn’t.

11. @Alex

Good work! Can’t fault that! I make that 156, a bit more than John’s adaptation of Snakehips’ solution, which is currently leading at 139 characters.

Some nice, instructive technique there, however, so well done!

Cheers

12. Elias says:

`=IFERROR(SMALL(IF(COUNTIF(A1:D5,A1:D5-1)+COUNTIF(A1:D5,A1:D5+1),A1:D5),ROW(INDIRECT("1:1048576"))),"")`

Regards

13. @Elias

Thanks a lot, but unfortunately that returns an array with more than a million extraneous entries, all of them blanks!

Regards

14. Alex Groberman says:

The combined solution!

`=SMALL(IF(A1:D5*(COUNTIF(A1:D5,A1:D5+1)+COUNTIF(A1:D5,A1:D5-1)),A1:D5),ROW(A1:A50))`
15. @Alex

Again – as per my reply to Elias.

Regards

16. Elias says:

@XOR

Does that really matter? It does not impact the calculation performance unless you enter the formula into those million records.

In any of the other options you will get an error if you enter the formula in more rows than records in the array.

Regards

17. @Elias

Whether it “matters” to you from a practical point of view or not is a moot point!! That’s the challenge that has been set!

Remember: the values returned in any worksheet cells are not of interest here. What is of interest is the array output from any construction, which should contain only those elements as defined. Nothing else.

Regards

18. @Alex

I take it back. You appear to have made the assumption that there is an upper limit of 50 for the values in the range, though I gave no such indication that this was the case.

Regards

19. Elias says:

@XOR

Regards

20. @Elias

Apologies if my use of that punctuation caused you to be offended. I can assure you that no such offence was intended.

Regards

21. Alex Groberman says:

Alright these solutions borrow from all:

`=MODE.MULT(IFERROR(FREQUENCY(IF(COUNTIF(A1:D5,A1:D5+1)+COUNTIF(A1:D5,A1:D5-1),A1:D5),ROW(A1:A50))^{0,0}*ROW(A1:A51),FALSE))`
`=MODE.MULT(IFERROR(SMALL(IF(A1:D5*(COUNTIF(A1:D5,A1:D5+1)+COUNTIF(A1:D5,A1:D5-1)),A1:D5),ROW(A1:A50))*{1,1},FALSE))`

The second one is slightly shorter, but both are interesting.

-Alex

22. Alex Groberman says:

Oops! Replace the second one with this:

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

Now it’s definitely the shortest!

23. John Jairo V says:

Great and Ingenious Solution @Alex Groberman! Well Done!

24. @Alex

Indeed! Excellent stuff! Current leader at 103 characters.

Cheers

I did not understand the challenge until I pasted part of Alex’s latest formula in a spreadsheet. If I steal the COUNTIF idea being used here and apply it to Google Sheets, this is what I get

`=sort(transpose(split(concatenate(if(countif({A1:D5+1;A1:D5-1},A1:D5),A1:D5&"@",)),"@")))`

88 characters without the equals sign.

26. @Isai

Beaten again by the “Sheets Master”! 🙂

Cheers!

27. Maxim Zelensky says:

Hi!

Lets save 2 more chars from Alex’s formula by reducing column names from ROW():

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

But IMHO ROW(1:20) or ROW (1:50) is a light cheat 🙂 – you need to calculate it before writing formula 🙂

28. @Maxim

You can’t do that! Re-read my pre-amble: “Any ranges must include both a row and column reference: A:D or 1:5, for example, are not acceptable.”

Nice try though!

29. David says:

Hi XOR LX ,

With Morefunc ,

`=MMIN(IF(COUNTIF(A1:D5,A1:D5+1)+COUNTIF(A1:D5,A1:D5-1),A1:D5))`

Regards

David

30. @David

Thanks, but such add-ins are “cheating” here! 🙂

31. This is a very instructive – and very hard – challenge. I had no idea that you could feed a 2d array in as the Critera range for COUNTIF. And I had no idea that you could feed an IF statement with an array of numbers. So much learning.

Here’s my monster, that I constructed without the help of these two tricks:

`=SMALL(IF(FREQUENCY(IF(MMULT(SMALL(Data,ROW(INDIRECT("1:"&COUNT(Data)-1))+{0,1})*{-1,1},{1;1})=1,SMALL(Data,ROW(INDIRECT("1:"&COUNT(Data)-1))+{0,1})),ROW(INDIRECT("1:"&MAX(Data))))>0,ROW(INDIRECT("1:"&MAX(Data)))),ROW(INDIRECT("1:"&COUNT(IF(FREQUENCY(IF(MMULT(SMALL(Data,ROW(INDIRECT("1:"&COUNT(Data)-1))+{0,1})*{-1,1},{1;1})=1,SMALL(Data,ROW(INDIRECT("1:"&COUNT(Data)-1))+{0,1})),ROW(INDIRECT("1:"&MAX(Data))))>0,ROW(INDIRECT("1:"&MAX(Data)))))))) `
32. Peter B says:

Good thing I didn’t see this at the time. Everything I think or do seems to violate one rule or another!

To determine whether a number passes or fails the adjacency test the named formula ‘p?’ refers to

`=IF(COUNTIF(v,v+1)+COUNTIF(v,v-1),1)`

Next one needs an function that returns an index (unlike the misnamed INDEX function that returns a range reference). Without that, one is lumbered with ‘k’ that refers to

`=ROW(INDIRECT("A1:A"&COUNT(p?)))`

Finally the solution ‘s’ refers to

`=SMALL(IF(p?,v),k)`

This site uses Akismet to reduce spam. Learn how your comment data is processed.