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!
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!
@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
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!
=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))))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}))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}))@John Jairo V
Hi! And, yes, I’m happy with that adaptation of Snakehips’ solution. Good stuff!
Cheers
@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.
Regards
@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
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.
@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
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
@Elias
Thanks a lot, but unfortunately that returns an array with more than a million extraneous entries, all of them blanks!
Regards
The combined solution!
@Alex
Again – 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
@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
@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
@XOR
I see your point. Just one question, where those exclamation points really necessary in your reply?
Regards
@Elias
Apologies 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:
=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
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!
Great and Ingenious Solution @Alex Groberman! Well Done!
@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.
@Isai
Beaten again by the “Sheets Master”! 🙂
Cheers!
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 🙂
@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!
Hi XOR LX ,
With Morefunc ,
Regards
David
@David
Thanks, 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:
=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))))))))