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.

You can download the workbook here.

Solution next week. Best of luck!

### Like this:

Like Loading...

*Related*

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!

@SnakehipsAnd a very good starter indeed! Congratulations!

I make that

166characters (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

Hi!

With @Snakehips approach… could be shorter like this:

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

Blessings!

Here’s my crude attempt:

Slight revision to save characters!

@John Jairo VHi! And, yes, I’m happy with that adaptation of Snakehips’ solution. Good stuff!

Cheers

@ChrisBMHi, 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 Formulatool to take a look at what happens when you try to apply an OR construction like that within an array formula.Regards

@Alex GrobermanThanks 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

Thanks for debugging it!

Fixed:

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.

@AlexGood work! Can’t fault that! I make that

156, a bit more than John’s adaptation of Snakehips’ solution, which is currently leading at139characters.Some nice, instructive technique there, however, so well done!

Cheers

Adapting John’s adaptation of Snakehips’

Regards

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

Regards

The combined solution!

@AlexAgain – as per my reply to Elias.

Regards

@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

@EliasWhether 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 cellsare not of interest here. What is of interest is thearrayoutput from any construction, which should contain only those elements as defined. Nothing else.Regards

@AlexI 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

@XOR

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

Regards

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

Regards

Alright these solutions borrow from all:

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

-Alex

Oops! Replace the second one with this:

Now it’s definitely the shortest!

Great and Ingenious Solution @Alex Groberman! Well Done!

@AlexIndeed! Excellent stuff! Current leader at

103characters.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

88 characters without the equals sign.

@IsaiBeaten again by the “Sheets Master”! 🙂

Cheers!

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 🙂

@MaximYou 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!

Hi XOR LX ,

With Morefunc ,

Regards

David

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

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

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:

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