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
Yes, I know that MATLAB's randperm() function would be perfect here, but this is an E-X-C-E-L challenge
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...
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).
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.
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.
Here's what I've got: Code: =INDEX(Cards,(LARGE(ROW($1:$52)*NOT(COUNTIF($B$1:B1,Cards)),RANDBETWEEN(1,53-ROW(B1))))) Which is pretty close to tiger's I've used ROW($1:$52) instead of (ROW(Cards)-1) to save a few characters.
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.
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.