Excel Challenge #7

Got Excel skills? Let's see.

It's a return to playing cards for this month's challenge. I suspect this one will be more about the optimisation than actually coming up with a working solution, but we'll have to wait and see about that.

Here are the usual rules.

• Create a single Excel formula to solve the stated problem.
• The shortest formula wins (when calculating the length of the formula there's no need to include the leading "=", or the curly braces that Excel uses to represent array formulae).
• The formula's number format can be set to whatever you want.
• Conditional formatting is not allowed.

Problem #7

Cell A1 contains a string representing a hand of cards; the number cards (2-10) are worth their face value, the Jack ("J"), Queen ("Q") and King ("K") are all worth ten, and the Ace ("A") can be either 1 or eleven. Create a single formula to evaluate the strength of the hand when playing Blackjack, ie it should return the highest possible value less than or equal to 21. If there's no way to obtain a hand value below 22 then the function should return "bust". For example:

A1
"468" --> "18"
"4684" --> "bust"
"A4726" --> "20" not "30"
"AA9" --> "21" not "11" or "31"
"K7" --> "17"
"KQ4" --> "bust"

Have fun

 

Wow. That's a tremendous start! The previous Challenges have shown that winning entries can be around half the length of the first submission. Let's see how it goes this time...

 

Here's a partial solution (it only displays the hand value without saying "bust" if it's over 21) with 199 characters

Code:

=SUM(IFERROR(--MID(SUBSTITUTE(A1,"A","1"),ROW(INDIRECT("1:"&LEN(A1))),1),10))+IF(AND(NOT(ISERROR(SEARCH("A",A1))),SUM(IFERROR(--MID(SUBSTITUTE(A1,"A","1"),ROW(INDIRECT("1:"&LEN(A1))),1),10))<12),10,0)

It works by calculating the base value, x, of the hand (assuming that the Ace is worth 1) and then checking to see if one of the Aces should be promoted to a value of 11 instead (by adding 10). Therefore the formula essentially looks like

Code:

 = x + IF(x<12 AND {hand contains an Ace}, 10, 0)

The problem here is that x needs to be evaluated twice, and that makes the formula excessively long. Any ideas?

 

Here we go;

Code:

=IFERROR(VLOOKUP(21,SUM(IFERROR(--MID(SUBSTITUTE(A1,"A","1"),ROW(INDIRECT("1:"&LEN(A1))),1),10))+NOT(ISERROR(SEARCH("A",A1)))*{0;10},1),"bust")

Character count is 142.

It creates an array of the two possible values any hand can take (ie by counting the first Ace that's present as either 1 or 11 - in the event of no Aces then both values in the array are the same), and then uses a VLOOKUP function to find the value 21. If an exact match is not found, the default setting on VLOOKUP is to return the highest value closest to the target amount. This is essentially a conditional MAX function as required.

 

Looks like it's just you and me this time!

Great suggestions for reducing the character count, here are a couple more:

1. the quote marks around the 1 in the SUBSTITUTE function can go
2. LOOKUP will do instead of VLOOKUP, which apart from the obvious improvement of losing the "V", LOOKUP doesn't need the ",1" either.

This gives:

Code:

=IFERROR(LOOKUP(21,SUM(IFERROR(--MID(SUBSTITUTE(A1,"A",1),ROW(OFFSET(A1,,,LEN(A1))),1),10))+ISNUMBER(FIND("A",A1))*{0;10}),"bust")

Character count: 129

 

Turned out to be a tricky one after all

Watch out for Excel Challenge #8 - launching Friday 11th September 2015.