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

  • The chosen word may be placed anywhere on the board, either horizontally (reading left-to-right) or vertically (reading top-to-bottom), 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 double-letter tiles with double-word 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!

31 comments

  1. 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:

    =INDIRECT("Q"&ROUND(1/MOD(MAX(MMULT(LOOKUP(MID(Q5:Q77,COLUMN(A1:G1),COLUMN(A1:G1)^0),U2:U28,V2:V28),ROW(A1:A7)^0)*(1+(LEN(Q5:Q77)>4))+1/ROW(Q5:Q77)),1),))

    This will work for any max-7-letter 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

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

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

    =MAX(MMULT(SUMIF(U3:U28,MID(Q5:Q77,{1,2,3,4,5,6,7},{1}),V3:V28),{1;1;1;1;1;1;1}))

    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!

  4. 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! 🙂

  5. Hi again,

    As it happens your restriction caused me to re-examine and optimize a few things:

    =INDIRECT("Q"&ROUND(1/MOD(MAX(MMULT(IFNA(LOOKUP(MID(Q5:Q77,COLUMN(A1:G1),1),U3:V28),),ROW(A1:A7)^0)*(1+(LEN(Q5:Q77)>4))+1/ROW(Q5:Q77)),1),))

    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

  6. @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 post-2010 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!

  7. One more adjustment to save a few chars:

    =INDIRECT("Q"&MOD(MAX(MMULT(IFNA(LOOKUP(MID(Q5:Q77,COLUMN(A1:G1),1),U3:V28),),ROW(A1:A7)^0*99)*(1+(LEN(Q5:Q77)>4))+ROW(Q5:Q77)),99))

    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

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

    =INDIRECT("Q"&MOD(MAX(MMULT(SUMIF(U3:U28,MID(Q5:Q77,{1,2,3,4,5},1),V3:V28),{99;99;99;99;99})*(1+(LEN(Q5:Q77)>4))+ROW(Q5:Q77)),99))

    That weighs in at 128 by my reckoning, with not an IFNA in sight!

    Will I get away with it?

  9. Sorry !! Posted wrong formula.
    Also, just 1 direct array substitution.

    =INDIRECT("Q"&MOD(MAX(MMULT(SUMIF(U3:U28,MID(Q5:Q77,{1,2,3,4,5},1),V3:V28),ROW(A1:A5)^0*99)*(1+(LEN(Q5:Q77)>4))+ROW(Q5:Q77)),99))
  10. Hi XORLX!

    This formulae is similar to Alex Groberman (CSE):

    =LOOKUP(1,0/FREQUENCY(0,1/MMULT(IFERROR(LOOKUP(MID(Q5:Q77,COLUMN(A1:G1),1),U3:V28),),ROW(A1:A7)^0)*(1+(LEN(Q5:Q77)>4))),Q5:Q77)

    I Use IFERROR instead of IFNA for your comment. Blessings!

  11. Hi again!

    Another try (with Idea of SnakeHips)

    =LOOKUP(1,0/FREQUENCY(0,1/MMULT(SUMIF(U3:U28,MID(Q5:Q77,{1,2,3,4,5},1),V3:V28),{1;1;1;1;1})*(1+(LEN(Q5:Q77)>4))),Q5:Q77)

    Blessings!

  12. 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! 🙂

  13. Then… put this formula:

    =LOOKUP(1,0/FREQUENCY(0,1/MMULT(SUMIF(U3:U28,MID(Q5:Q77,COLUMN(A1:G1),1),V3:V28),ROW(A1:A7)^0)*(1+(LEN(Q5:Q77)>4))),Q5:Q77)

    122. Blessings!

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

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

  16. Tiny alteration of John’s formula:

    =LOOKUP(1,0/FREQUENCY(0,1/MMULT(SUMIF(U3:U28,MID(Q5:Q77,COLUMN(A1:G1),1),V3),ROW(A1:A7)^0)/(1+(LEN(Q5:Q77)>4))),Q5:Q77)

    The SUMIF automatically expands V3 to V3:V28 (although this makes the function volatile if I remember correctly).

    Now at 118.

    -Alex

  17. Ah, yes! I had:

    =LOOKUP(1,0/FREQUENCY(0,1/((1+(LEN(Q5:Q77)>4))*MMULT(SUMIF(U3:U28,MID(Q5:Q77,COLUMN(A1:G1),1),V3),ROW(A1:A7)^0))),Q5:Q77)

    originally, though I seem to have a couple of extraneous parentheses compared to this double-division 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!

  18. Thx Lori for your fix… XOR LX for the challenge and explanations, and all for participate.

    Now at 116:

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

    Lori could show us the “tricky” solution with less characters (allways having great solutions and techniques).

    Blessings!

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

    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:

    =IF(1,LOOKUP(3,{1;2;3},C1:C2),SUMIF(A1:A3,1,B1:B3))

    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.

  20. @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:

    =LOOKUP(0,0/{0,0,1,0,0,1,0,0,0,0,0,0,0},{1,2,3,4,5,6,7,8,9,10,11,12,13})

    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.

  21. Hi friend ,
    Not sure I understand the dynamics really, you mean it,

    =INDEX(Q5:Q77,MMULT(SUMIF(U3:U28,MID(Q5:Q77,COLUMN(A1:G1),1),V3)*(LEN(Q5:Q77)>4),1^N(+A1:A7))*ROW(Q5:Q77)-4)

    or

    =INDEX(Q5:Q77,MMULT(SUMIF(U3:U28,MID(Q5:Q77,COLUMN(A1:G1),1),V3),1^N(+A1:A7))*ROW(Q5:Q77)-4)

    David

  22. I take no credit for Lori’s true excellence, but here’s 108 by using the first seven letter-values (which we know are numbers) instead of A1:A7:

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

    -Alex

  23. 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!

  24. @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]……)

  25. @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

  26. Just for fun if you have the new functions that sam mentioned (cse):

    =FILTERXML("<a>"&CONCAT("<b>4)/MMULT(SUMIF(U3:U28,MID(Q5:Q77,COLUMN(A1:G1),1),V3),1^V3:V9))&""">"&Q5:Q78&"")&"","//b[@c=1]")

    Far from shortest but who knows, maybe XPATH will end up being a neat go-to in the future!

    -Alex

  27. @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

  28. Pingback: Shortest Formula Challenge #6: Results and Discussion « EXCELXOR

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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s