1. Posts in the subject areas are now being moderated. Please do not post any details about your exam for at least 3 working days. You may not see your post appear for a day or two. See the 'Forum help' thread entitled 'Using forums during exam period' for further information. Wishing you the best of luck with your exams.
    Dismiss Notice

Excel Challenge #4

Discussion in 'Off-topic' started by Steve Hales, May 15, 2015.

  1. Steve Hales

    Steve Hales ActEd Tutor Staff Member

    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
     

    Attached Files:

    Steve Hales, May 15, 2015
    #1
  2. Steve Hales

    Steve Hales ActEd Tutor Staff Member

    Yes, I know that MATLAB's randperm() function would be perfect here, but this is an E-X-C-E-L challenge :D
     
    Steve Hales, May 15, 2015
    #2
  3. Oxymoron

    Oxymoron Ton up Member

    Dang :D
     
    Oxymoron, May 15, 2015
    #3
  4. Shillington

    Shillington Member

    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...
     
    Shillington, May 16, 2015
    #4
  5. didster

    didster Member

    Wonder what qualifies as a random sequence here.
    Shillington, how did you calculate the 24 paths?
     
    didster, May 17, 2015
    #5
  6. Shillington

    Shillington Member

    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).
     
    Shillington, May 17, 2015
    #6
  7. didster

    didster Member

    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.
     
    didster, May 17, 2015
    #7
  8. Steve Hales

    Steve Hales ActEd Tutor Staff Member

    Every time you hit F9 to recalculate you get a different ordering.
     
    Steve Hales, May 18, 2015
    #8
  9. tiger

    tiger Ton up Member

    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.
     
    Last edited: May 18, 2015
    tiger, May 18, 2015
    #9
  10. Shillington

    Shillington Member

    I am also rusty, I should have said n*a rather than a^n.
     
    Shillington, May 18, 2015
    #10
  11. Steve Hales

    Steve Hales ActEd Tutor Staff Member

    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.
     
    Steve Hales, May 18, 2015
    #11
  12. tiger

    tiger Ton up Member

    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.
     
    tiger, May 19, 2015
    #12
  13. Steve Hales

    Steve Hales ActEd Tutor Staff Member

    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 :D

    Watch out for Excel Challenge #5 - launching Friday 12th June 2015.
     
    Steve Hales, May 22, 2015
    #13

Share This Page