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

The chosen word may be placed anywhere on the board, either horizontally (reading lefttoright) or vertically (reading toptobottom), though not diagonally, on condition that the central square (◊) is covered.

The score for a given word is obtained via looking up the individual value – using the table supplied (U3:V28) – for each of the letters within that word and totalling those values.

If the chosen word covers any of the squares marked “2W” then the score for that word is doubled. (Readers familiar with the game of Scrabble will notice that I have taken the liberty of replacing doubleletter tiles with doubleword ones for the sake of simplification. It might also be pointed out that the usual doubling of score for words passing through the central square is here a moot point.)

If more than one word share the highest possible score, then any one of those words may be returned.
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:O or 5:77, for example, are not acceptable. Named Ranges are also not permitted.
Solution next week. Best of luck!
Ok…..
If you permit me to change U2 to a single apostrophe (i.e. ‘) and V2 to the number 0, then we can get this formula:
This will work for any max7letter word list. If you don’t permit my little change to U2 and V2, then error handling will need to be introduced for potential words shorter than seven letters. 155 characters! (including the {}’s)
Regards,
Alex
Many thanks, Alex!
It brings me no pleasure to have to tell you that your requested amendments to the worksheet are not permissible, however. Sorry!
Regards
Ok, so I was extremely surprised and embarrassed when I asked for help on a different site and YOU actually responded. I felt pretty bad. I really did. But then I thought about my use of the mid function which I took from your site as well. So I guess that makes me a big cheater. Well if it’s any consolation I don’t have the time nor the knowledge to correctly finish this. But I’m going to put my stuff out there anyway.
It will give you the max value of the words listed but won’t increase in value if over a bonus tile.
Thanks for the site!
Thanks, Will! Good start! So perhaps others can develop what you’ve posted into a full solution.
Not sure about the necessity for {1} as opposed to simply 1 here, though?
Thanks for the input! 🙂
Hi again,
As it happens your restriction caused me to reexamine and optimize a few things:
Coming in at 141 characters including the {}’s, which is actually shorter than my first entry which omitted the error checking!
Thanks for devising such a great challenge!
Alex
@Alex
Not bad at all! 🙂 I particularly like your novel and ingenious method of returning an entry corresponding to a maximum.
Actually, I had intended to exclude any post2010 solutions (mainly because I’m still on that version! – I know, shocking, isn’t it?), though I’m pretty sure I can work out how IFNA operates (!), so I’ll let you have that one!
FYI I don’t punish the use of CSE formulas, so you’re currently setting the pace at 139 characters.
Cheers!
One more adjustment to save a few chars:
This only supports up to the 98th row, but comes in at 131 chars (ignoring CSE)
You could substitute 6^8 for 99 in the formula, which is ~ 1.7 million so any length of word lists would work, and would only add 2 characters to the above answer, making it 133 long.
Another interesting effect is this returns the last highest word instead of the first one.
Alex
I feel a bit of a cheapskate since the following is something of a rip off, combining work done by Alex & Will.
Also it assumes that the question is specific to the stated rack and it’s list of possible words. That being the case there is no need for the formula to be able to deal with words of more than 5 letters*. Thus a couple of array generating elements can be replaced by fixed arrays in order to shave off a couple of characters.
That weighs in at 128 by my reckoning, with not an IFNA in sight!
Will I get away with it?
Sorry !! Posted wrong formula.
Also, just 1 direct array substitution.
Hi XORLX!
This formulae is similar to Alex Groberman (CSE):
I Use IFERROR instead of IFNA for your comment. Blessings!
Hi again!
Another try (with Idea of SnakeHips)
Blessings!
Cheers, John! Cheers, Snakehips!
But sorry – although this particular rack does not give rise to any words of more than 5 letters, nevertheless that possibility needs to be accounted for!
Cheers! 🙂
Then… put this formula:
122. Blessings!
Great stuff, John! 🙂
Current leader at 122!
I’ve got 120 at the moment (using 2010), so I’m hoping someone can go even better still!
Cheers
I originally had 120 chars too but didn’t think of Will’s elegant SUMIF construction. I think we can maybe squeeze even 10 more characters out by using a couple of tricks and ideas from shortest challenge 3.
(In addition I believe the * should be changed to / in John’s formula e.g. try with “tzars” in the list)
Tiny alteration of John’s formula:
The SUMIF automatically expands V3 to V3:V28 (although this makes the function volatile if I remember correctly).
Now at 118.
Alex
Ah, yes! I had:
originally, though I seem to have a couple of extraneous parentheses compared to this doubledivision version (parentheses which I felt were necessary since, as Lori mentions, without them, as in John’s case, the construction is flawed logically).
Good stuff! Collaboratively getting lower and lower!
118 indeed!
Thx Lori for your fix… XOR LX for the challenge and explanations, and all for participate.
Now at 116:
Lori could show us the “tricky” solution with less characters (allways having great solutions and techniques).
Blessings!
Ah! Another shocking abuse of range referencing!
What would Excel purists say, eh? 🙂
Cheers, John!
is 112 – maybe that’s the limit?
Notes:
– The LOOKUP argument Q5:Q6 expands to Q5:Q78 since FREQUENCY returns one more row. In practice you may want to insert a plus sign to restrict this behaviour i.e. +Q5:Q77.
– The reduced sum range causes the SUMIF formula to be volatile as can be seen by using a simplified formula like:
This returns the value in C3 but does not update when C3 is changed (since C3 is not referenced and is not a formula precedent.) However changing B3 to B2 does cause the formula to update when C3 is changed as it is now volatile.
@Lori
Wonderful stuff! 🙂
Forget division! Indexing is the way to go!
And of course we all missed this nice alternative for generating a unit array – another important use for the unary plus, eh?
A small point, but I feel it might perhaps be worthwhile pointing out to readers that the use of 0 as the lookup_value within LOOKUP only works here since, in the case of more than one word sharing the highest score, we are indifferent to which is returned.
In general, of course, if this value is not strictly greater than all values within the lookup_vector, then we are not guaranteed correct results. For example:
returns 3, not the correct 6. Replacing the 0 with a lookup_value of, for example, 1 corrects this error.
I mentioned the volatility of SUMIF with such a syntax in my last post. It’s an interesting feature, though I also can’t see why (apart from in shortest formula challenges such as this!) one would ever prefer such a syntax over the standard one. Still, perhaps I’ll be proved wrong.
Great stuff! Many thanks.
Hi friend ,
Not sure I understand the dynamics really, you mean it,
or
David
I take no credit for Lori’s true excellence, but here’s 108 by using the first seven lettervalues (which we know are numbers) instead of A1:A7:
Alex
Ignore my last comment re the choice of lookup_value. As Lori points out, the FREQUENCY construction will, in this case, only ever consist of one occurrence of a 1.
@Alex Yes! I believe that I used to add a caveat to some of my earlier challenges along the lines of “no assumptions about the contents of any worksheet cells can be made”, though of course, as you say, those seven cells will indeed always contain numbers, so nice thinking!
@XLOR LX
Short Formula challenges are going to become shorter
After yesterday update – Excel 2016 (Professional Plus – MSI version) has 3 new functions
1) CONCAT – Finally
2) IFS
3) SWITCH
1 ) The syntax of CONCAT is = CONCAT(Text1,[Text2],…..)
Text1 accepts – String, Cell or Cells
So you can now say = CONCAT(A1:A10)
The String1 – accepts ranges as well !!
Unfortunately you don’t have a “Delimiter” as a parameter. So if you wish to introduce a delimiter then it has to be array entered as so
= CONCAT(A1:A10&” “)
2) IFS : avoids nested IF’s : IF (Logical1, Value1, [Logical2, Value2]….)
Optional Parameters are in Pairs – So you need to build the logic Top Down
3) SWITCH : is a bit different from the Database version of SWITCH
SWITCH (Expression, Value1, Result1, [Value2, Result2]……)
@XORLX the short formula challenges are going to get shorter
Excel 2016 (after the Jan update) has a few new formulas
1) CONCAT and TEXTJOIN – Finally
2) IFS
3) SWITCH
4) MINIFS / MAXIFS
1 ) The syntax of CONCAT is = CONCAT(Text1,[Text2],…..)
Text1 accepts – String, Cell or Cells
So you can now say = CONCAT(A1:A10)
The String1 – accepts ranges and arrays as well !! – both 1d and 2d – and unequal sizes as well
Unfortunately you don’t have a “Delimiter” as a parameter. So if you wish to introduce a delimiter then it has to be array entered as so
= CONCAT(A1:A10&” “)
2) The syntax of TEXTJOIN is a below
=TEXTJOIN(delimiter,ignore_empty,text1,[text2]….)
The text1– accepts ranges and arrays as well !! – both 1d and 2d – and unequal sizes as well
3) IFS : avoids nested IF’s : IF (Logical1, Value1, [Logical2, Value2]….)
Optional Parameters are in Pairs – So you need to build the logic Top Down
4) SWITCH : is a bit different from the Database version of SWITCH
SWITCH (Expression, Value1, Result1, [Value2, Result2]……)
5)MINIFS and MAXIFS – similar to their SUM/COUNT/AVERAGE versions
Just for fun if you have the new functions that sam mentioned (cse):
Far from shortest but who knows, maybe XPATH will end up being a neat goto in the future!
Alex
@sam
That’s fantastic!
Many thanks for sharing all this with us. I’ve often complained about the lack of such capability among the text functions, and I won’t say that it’s not long overdue, but still, at least soon we’ll be able to perform such seemingly elementary operations using worksheet formulas alone.
Many thanks again!
Cheers
Pingback: Shortest Formula Challenge #6: Results and Discussion « EXCELXOR
i just discovered the blog,so many tips.. and i found it just amazing, can’t wait to check all the posts, excel is just awesome.
keep sharing!
Thanks
@Aysdi
Glad you think so. And, yes, Excel is indeed awesome! 🙂
Regards
Pingback: Excel Roundup 20160201 – Contextures Blog
Pingback: Video 00161 Extract Matrix Non Blanks Into 1 Column  My Spreadsheet Lab