Shortest Formula Challenge #6: Results and Discussion Reply

A couple of weeks ago I set readers the challenge which can be found here.

Once again, some truly excellent responses and a noticeably collaborative attempt towards obtaining our final, minimal-length solution. So many thanks to all who contributed: Alex, John Jairo, Lori, Snakehips and Will!

And that solution, at 108 characters, is:

=LOOKUP(,0/FREQUENCY(0,2^-(LEN(Q5:Q77)>4)/MMULT(SUMIF(U3:U28,MID(Q5:Q77,COLUMN(A1:G1),1),V3),1^V3:V9)),Q5:Q6)

How does it work?

More…

Shortest Formula Challenge #6: Scrabbled 31

The challenge this week is as follows (you can download the workbook here):

It is you to start in a game of Scrabble, and your rack of letters (cell Q2) is DGTAROZ.

You are a strong enough player to have deduced a list of all possible words from the Engligh language of two or more letters which can be formed from this rack. Given that list in Q5:Q77, derive a single formula to return the word from that list which returns the highest score when placed on the board (A5:O15).

Scrabbled

More…

Shortest Formula Challenge #5: Results and Discussion 2

A couple of weeks ago I set readers the challenge which can be found here.

Surprisingly, no-one managed to come up with the shortest solution. Dozens of attempts came very close, yet all were either ineligible or syntactically incorrect.

As such, the shortest correct solution was also the very first received, courtesy of pjc, viz:

2*ROW(A1:A2)-3

So many congratulations to pjc, even the more so as this was their very first post at this site! Fantastic start, pjc!

More…

Shortest Formula Challenge #5: No Array Constants, Please 18

I was recently helping someone to develop a formula-based Conditional Formatting rule, a stipulation for which (at least in 2010) is that the explicit use of array constants is forbidden.

Of course, we can get around this by first storing these array constants as Defined Names and then referencing those in the formula instead, which is perfectly legitimate.

On that particular day, however, I was in a rather creative mood (no doubt to the OP’s detriment!), and so went about the (somewhat convoluted) task of creating a version of my formula in which any array constants were replaced with suitable functional constructions, only to find that this process was not always quite as straightforward as I had first presumed…

More…

Shortest Formula Challenge #4: Results and Discussion 5

Last week I set readers the challenge which can be found here.

A good response to this one, leading to a solution for which, in the end, most people who responded can take some credit.

Snakehips started the ball rolling with a nice logical construction involving “OR”ing two separate COUNTIFs; John Jairo V then shaved off several characters from this solution; this was then further refined by Elias; and, finally, after several attempts at constructing a solution using FREQUENCY, Alex Groberman took the COUNTIF set-up and wrapped it in that most wonderful of functions – MODE.MULT – to give us our winner.

More…

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}

More…

Shortest Formula Challenge #3: Results and Discussion 1

Last week I set readers the challenge which can be found here.

This one provoked quite a bit of debate, and not all of it Excel-related! As I already have to several readers, I must again apologize for the lack of realism and statistical know-how inherent in the premise for this challenge, which was evidently constructed more with the required formula-work in mind than with any serious thought to methods in demography.

Still, at least some fascinating and impressive Excel work came out of it all, so perhaps my poor groundwork is somewhat forgiven, at least retrospectively!

More…

Shortest Formula Challenge #3: World Populations 47

The challenge this week is as follows: based on the below data extract of populations for a selection of countries for the four years from 2010-2013 inclusive, a single formula in A1 to identify the Region which had the largest average yearly percentage increase in population over that period.

World Populations

The answer here would be Sub-Saharan Africa. For readers wishing to corroborate their results, the average yearly percentage increase in population for that region was 2.9840070521% to 10 d.p.

More…

Shortest Formula Challenge #2: Results and Discussion 5

Last week I set readers the challenge which can be found here.

5 correct solutions received, courtesy of John Jairo V, GreasySpot, Bill Szysz, James and ChrisBM (who actually missed off a final parenthesis in his formula, though I will be lenient here!). So well done to all!

As to whose was the shortest, excluding the offering from Isai Alvarado, who beat everyone with his 51-character (excluding the equals sign) Google Sheets construction (well done Isai!), that accolade is shared by John and Bill, both of whose solutions came in at 56 characters, which is quite a remarkable coincidence when you consider that each used a completely different construction! So congratulations to John and Bill!

More…

Shortest Formula Challenge #1: Results and Discussion 2

Last week I set readers the challenge which can be found here.

Two correct answers received (three if you count Snakehips‘ improvement) from GreasySpot and Bill Szysz, of which Bill’s was the shorter of the two (74 characters compared to 249, excluding the equals sign).

Snakehips then came along and improved this to a mere 70 characters simply by making the reference to the required range relative. (I can imagine Bill is now kicking himself for using absolute references in a shortest-formula challenge!)

Anyway, between the two of them they managed to come up with what is indeed (at least, that I know of) the shortest possible solution to this problem, and that solution is:

More…