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:

Consecutive Integers

one possible answer would be (as highlighted):


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:


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!


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


    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!


  3. Hi!

    With @Snakehips approach… could be shorter like this:


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


  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:

  6. Slight revision to save characters!

  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:


    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.


  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?


  9. Thanks for debugging it!



    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!


  11. Adapting John’s adaptation of Snakehips’



  12. @Elias

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


  13. The combined solution!

  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.


  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.


  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.


  17. @XOR

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


  18. @Elias

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


  19. Alright these solutions borrow from all:


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


  20. Oops! Replace the second one with this:


    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


    88 characters without the equals sign.

  22. Hi!

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


    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 ,




  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:

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


    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


    Finally the solution ‘s’ refers to


Leave a Reply

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

You are commenting using your 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 )

Connecting to %s

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