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).
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!
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…
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}
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!
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.
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.
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!
The table on the left shows the results of a test in which a list of countries was provided (in A3:A12) and the student asked to list both the capital city and currency for each of those countries.
Not all of these answers are correct, as can be verified using the table on the right.
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:
The challenge this week is as follows: given a random – though legal. according to the laws of the game – position on a standard chessboard, here occupying the range A1:H8, a single formula to give the material evaluation of the position. For example, on the board below:
the result of the formula would be -3.
EXCELXOR 