+ Reply to Thread
Results 1 to 6 of 6

Help with lottery

  1. #1
    Registered User
    Join Date
    12-11-2012
    Location
    Canada
    MS-Off Ver
    Excel Mac 2011
    Posts
    5

    Help with lottery

    Hi,

    We have series of external events that people at our office want to attend. We are only able to send 1 person to each so our intention is to hold a small lottery to choose who gets to go. I've attached the spreadsheet where people have signed up for their events of choice. I'm trying to figure out the best way to choose one person for each event. I foresee that one person may be chosen for multiple events so it would be best if that is avoided.

    I tried playing around with Rand & Index functions but didn't get the results I was hoping for. Any suggestions would be much appreciated.

    max

    lottery test.xlsx

  2. #2
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Help with lottery

    I would arrange the names in random order (e.g., pick names out of a hat), and as each is chosen, let them pick the event of their choice.

    Low tech.
    Entia non sunt multiplicanda sine necessitate

  3. #3
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 (Mac OSX) (16.83 (24031120))
    Posts
    8,686

    Re: Help with lottery

    this may be over complicated

    but i have created a table based on the X and number the X's in each row

    then used random to pick a number in that row
    then used index match to return a name

    F9 - re-calcs the choice

    in C
    =IF(C3="X",1,"")
    in D
    =IF(D3="X",MAX($C14:C14)+1,"")
    and copied to the other cells

    then used
    =RANDBETWEEN(1,MAX(C14:K14))

    to pick a number between and the max in that row

    then used
    =INDEX($C$2:$K$2,MATCH(L14,$C14:$K14,0))
    to identify the name

    you could just use
    =INDEX($C$2:$K$2,MATCH(RANDBETWEEN(1,MAX(C14:K14)),$C14:$K14,0))
    Attached Files Attached Files
    Last edited by etaf; 03-20-2014 at 08:09 PM.
    Wayne
    if my assistance has helped, and only if you wish to , there is an "* Add Reputation" on the left hand side - you can add to my reputation here

    If you have a solution to your thread - Please mark your thread solved do the following: >
    Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

  4. #4
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,091

    Re: Help with lottery

    Gut reaction ... low tech ... you, or their manager, should bite the bullet and allocate one person to each event dependent on either their appropriateness to attend the event or their development needs. If they are representing your organisation, it might be wise to put the weight on their knowledge, experience and skills.

    A lottery may salve your conscience but it sure won't appease the people who get sent to an event that is above or below their ability or of no interest to them.

    Be a manager

    Regards, TMS
    Trevor Shuttleworth - Retired Excel/VBA Consultant

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


  5. #5
    Registered User
    Join Date
    12-11-2012
    Location
    Canada
    MS-Off Ver
    Excel Mac 2011
    Posts
    5

    Re: Help with lottery

    Very interesting @etaf. It is a bit cumbersome but appears to do the trick. At the end of the day though I think @shg suggestion may be the best course of action as this is a really simple lottery

  6. #6
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 (Mac OSX) (16.83 (24031120))
    Posts
    8,686

    Re: Help with lottery

    yep, agree,but interesting excel exercise

    At one time i had out of the 8 events , 1 person going to 5 events
    so you may not want random

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Help me win the Lottery...
    By swade730 in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 10-15-2014, 07:56 AM
  2. lottery
    By heramiah in forum Excel General
    Replies: 14
    Last Post: 02-23-2010, 07:27 AM
  3. Lottery Numbers
    By MyLawd in forum Excel General
    Replies: 7
    Last Post: 09-12-2008, 10:20 AM
  4. Lottery Function
    By Giftschrank in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 05-01-2008, 10:41 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1