• We are pleased to announce that the winner of our Feedback Prize Draw for the Winter 2024-25 session and winning £150 of gift vouchers is Zhao Liang Tay. Congratulations to Zhao Liang. If you fancy winning £150 worth of gift vouchers (from a major UK store) for the Summer 2025 exam sitting for just a few minutes of your time throughout the session, please see our website at https://www.acted.co.uk/further-info.html?pat=feedback#feedback-prize for more information on how you can make sure your name is included in the draw at the end of the session.
  • Please be advised that the SP1, SP5 and SP7 X1 deadline is the 14th July and not the 17th June as first stated. Please accept out apologies for any confusion caused.

Excel Challenge #7

S

Steve Hales

ActEd Tutor
Staff member
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 :)
 
Last edited:
:cool: :D
Code: 
=IF(SUM(LOOKUP(SUM(--(MID(A1,ROW(1:11),1)="A")),{1,2,3,4},9+{2,3,4,5}),IFERROR(LOOKUP(MID(A1,ROW(1:11),1),{"J","Q","K"},{10,10,10}),0),IFERROR(--MID(A1,ROW(1:11),1),0))>21,IF(SUM(--(MID(A1,ROW(1:11),1)="A"),IFERROR(LOOKUP(MID(A1,ROW(1:11),1),{"J","Q","K"},{10,10,10}),0),IFERROR(--MID(A1,ROW(1:11),1),0))>21,"bust",SUM(--(MID(A1,ROW(1:11),1)="A"))),SUM(LOOKUP(--(MID(A1,ROW(1:11),1)="A"),{1,2,3,4},9+{2,3,4,5})))+SUM(IFERROR(LOOKUP(MID(A1,ROW(1:11),1),{"J","Q","K"},{10,10,10}),0),IFERROR(--MID(A1,ROW(1:11),1),0))
still length=513

Edit:-
Code: 
=IF(SUM(IFERROR(LOOKUP(MID(A1,ROW(1:10),1),{"J","Q","K"},{10,10,10}),0),IFERROR(LOOKUP(SUM(--(MID(A1,ROW(1:10),1)="A")),{1,2,3,4},9+{2,3,4,5}),0),IFERROR(--MID(A1,ROW(1:10),1),0))>21,IF(SUM(IFERROR(LOOKUP(MID(A1,ROW(1:10),1),{"J","Q","K"},{10,10,10}),0),--(MID(A1,ROW(1:10),1)="A"),IFERROR(--MID(A1,ROW(1:10),1),0))>21,"bust",SUM(IFERROR(LOOKUP(MID(A1,ROW(1:10),1),{"J","Q","K"},{10,10,10}),0),--(MID(A1,ROW(1:10),1)="A"),IFERROR(--MID(A1,ROW(1:10),1),0))),SUM(IFERROR(LOOKUP(MID(A1,ROW(1:10),1),{"J","Q","K"},{10,10,10}),0),IFERROR(LOOKUP(SUM(--(MID(A1,ROW(1:10),1)="A")),{1,2,3,4},9+{2,3,4,5}),0),IFERROR(--MID(A1,ROW(1:10),1),0)))
length=632
 
Last edited:
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.
 
seems, this time winning entry will have quarter length of first submission....

BTW,in your leading submission......
replacing
"INDIRECT("1:"&LEN(A1))" by "OFFSET(A1,,,LEN(A1))"
and
"NOT(ISERROR(SEARCH("A",A1)))" by "ISNUMBER(FIND("A",A1))"
Click to expand...
can decrease character count by 8.
 
Hemant Rupani said:
seems, this time winning entry will have quarter length of first submission....

BTW,in your leading submission......

can decrease character count by 8.
Click to expand...

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.
 
You must log in or register to reply here.
Back
Top