Shortest Formula Challenge #4: Consecutive Integers 32

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:

Consecutive Integers

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.

You can download the workbook here.

Solution next week. Best of luck!

32 comments

  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. 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. =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. 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. 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. @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

  8. @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

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

  10. @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

  11. Adapting John’s adaptation of Snakehips’

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

    Regards

  12. @Elias

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

    Regards

  13. The combined solution!

    =SMALL(IF(A1:D5*(COUNTIF(A1:D5,A1:D5+1)+COUNTIF(A1:D5,A1:D5-1)),A1:D5),ROW(A1:A50))
  14. @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

  15. @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

  16. @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

  17. @XOR

    I see your point. Just one question, where those exclamation points really necessary in your reply?

    Regards

  18. @Elias

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

    Regards

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

  20. 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!

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

  22. 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 🙂

  23. @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!

  24. Hi XOR LX ,

    With Morefunc ,

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

    Regards

    David

  25. Pingback: Shortest Formula Challenge #4: Results and Discussion « EXCELXOR

  26. 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)))))))) 

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s