Excel Challenge #4

[Steve Hales]

You've got Excel skills? Let's see.

Now that exams are out of the way, let's see if we can get some interesting solutions to this challenge. Be warned: this is a tricky one

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 #4

The objective here is to get Excel to shuffle a deck of cards. In the attached file there's an ordered deck (with named range "Cards"), and the challenge is to enter a formula in cell B2 (which should then be copied down to B53) so that each card appears only once but in a random order. Watch out for duplicates and missing cards. A check has been put in column C:C to verify that the formula works.

Good luck

 

[Steve Hales]

Yes, I know that MATLAB's randperm() function would be perfect here, but this is an E-X-C-E-L challenge

 

[Oxymoron]

Yes, I know that MATLAB's randperm() function would be perfect here, but this is an E-X-C-E-L challenge

Dang

 

[Shillington]

My idea would be to start off with a seed generated in the first cell which is prime to 52 and then generate the remaining slots using seed^n which would pass through each of 1-52.

It's not that random as there will only be 24 different paths based on this.

I don't know how to store a random number within a formula though so I can't proceed with this

Also assuming we can't use array formulas as you say put in your formula then copy it down...

 

[didster]

My idea would be to start off with a seed generated in the first cell which is prime to 52 and then generate the remaining slots using seed^n which would pass through each of 1-52.

It's not that random as there will only be 24 different paths based on this.

Wonder what qualifies as a random sequence here.
Shillington, how did you calculate the 24 paths?

 

[Shillington]

Wonder what qualifies as a random sequence here.
Shillington, how did you calculate the 24 paths?

Using my method you have to start with a integer coprime to 52.

Phi(52)=Phi(2^2*13)=Phi(2^2)*Phi(13)=2*12=24. Where Phi is Euler's totient function.

Obviously there are lots more sequences if you just rotate the seed (i.e. start at the second position not the first but I don't know how to code that).

 

[didster]

My Number theory is rusty but I don't think that will work.
a^ 24 = a^48 = 1 (mod 52)
So there will be repeated cards somewhere.
Nice idea though, as it might work with 53 if we can discard the extra card somehow, and then pass the "randomness" criteria.

 

[Steve Hales]

Wonder what qualifies as a random sequence here.

Every time you hit F9 to recalculate you get a different ordering.

 

[tiger]

My attempt:
=INDEX(Cards,LARGE((ROW(Cards)-1)*(1-COUNTIF(B$1:B1,Cards)),(51-COUNTA(B$1:B1))*RAND()+1))

edit: noticed the last card is not random. Will look at an update.

 

[Shillington]

My Number theory is rusty but I don't think that will work.
a^ 24 = a^48 = 1 (mod 52)
So there will be repeated cards somewhere.
Nice idea though, as it might work with 53 if we can discard the extra card somehow, and then pass the "randomness" criteria.

I am also rusty, I should have said n*a rather than a^n.

 

[Steve Hales]

Here's what I've got:

=INDEX(Cards,(LARGE(ROW($1:$52)*NOT(COUNTIF($B$1:B1,Cards)),RANDBETWEEN(1,53-ROW(B1)))))

Which is pretty close to tiger's

=INDEX(Cards,LARGE((ROW(Cards)-1)*(1-COUNTIF(B$1:B1,Cards)),(51-COUNTA(B$1:B1))*RAND()+1))

I've used ROW($1:$52) instead of (ROW(Cards)-1) to save a few characters.

 

[tiger]

I need to add an INT to mine:
=INDEX(Cards,LARGE((ROW(Cards)-1)*(1-COUNTIF(B$1:B1,Cards)),INT((52-COUNTA(B$1:B1))*RAND()+1)))

But yes the logic is the same.

 

[Steve Hales]

Looks like this one's been too challenging to yield a wide range of solutions. After a short diversion into number theory failed to produce the goods, tiger is this month's winner - well done

Watch out for Excel Challenge #5 - launching Friday 12th June 2015.