Oct 05 2020 09:52 PM
Oct 05 2020 09:52 PM
I am running an online event where I will have a list of raffle purchasers. I have seen in previous settings where excel has the functionality to run a program to select a winner randomly. One can see the program scrolling through all the names and then eventually landing on a winner.
Can someone tell me how to run such a program?
Oct 06 2020 05:07 AMSolution
I would be happy to know if I could help.
I know I don't know anything (Socrates)
Oct 06 2020 05:16 AM
Oct 11 2020 08:03 PM
Dear Nikolino, I am afraid the random function is not appropriate. I am not a 'coder' as such I need a formula that will provide the following:
If I have 120 Melb Cup Sweep purchasers and I have 24 horses in the race, If all these 24 horses names were put into a hat and each purchasers pulled out a name, I need for the 24 horse names to be randomly allocated to the 120 purchasers. There are 3 placings - 1st, 2nd & 3rd. When the race is run, there should be 5 people with the winning horse, 5 people who get second and 5 people who get third.
So I need a formula that firstly allocates the 24 horses to the purchasers but I need this to replicate as if they were being picked out of hat. So the Random function may not work as it doesn't do this. Plus it keeps changing the random number allocated which is not what I want.
I thought I might just allocate the horses to the purchasers based on date of purchase starting at Horse 1
See my file attached.
Oct 11 2020 10:06 PM
I think you can do that with a circular reference and with iterative calculation turned on (File/Options/Formulas - Enable iterative calculation - I set mine for 200).
To generate a new list, you can change the "Reset" drop down to TRUE, then hold down the F9 (calculate) key for a few seconds to randomize the list. Then, change the "Reset" to FALSE and hit/hold the F9 key until each horse appears 5 times (the "Condition Met" cell should change to TRUE).
If it were me, once the list is generated, I would copy/paste special value the list to another worksheet just to ensure I didn't accidentally trigger the formulas to recalculate later and cause the list to be re-shuffled.
Oct 12 2020 10:55 PM
I am sorry to bother you again but can you tell me how to do the second part
ie. Reset dropdown. I can't locate it.
It might be easier if you could make the change in the file that I sent. And I agree moving the final horse list to a new worksheet is what I would do also.
Oct 13 2020 10:55 PM
@JMB17 Hi Again, you are so clever. I would never have been able to work that out. I got the spreadsheet to output 5 horses for the selected buyers. So if I end up with say 300 buyers do I have to change the settings in formulas. You had put a setting at 200. Will I need to change that depending on the number of buyers I end up with ?
Is there anything else I should know. To ensure I don't mess up your formulas, I will just amend your spreadsheet and copy the buyer names to column A. If then I just update the Horse List values with the actual horse name, once I know it, will that impact the formulas. I am hoping not since it is referencing a cell.
It seems so much easier to cut up the names and put them in a hat. We would be doing that if we weren't living with a pandemic !! Thankyou so much for your help. You are amazing.
Oct 16 2020 10:04 AM
Sorry it took me a little while to get back to you.
I modified the workbook to use structured tables, which will make the lists easier to resize (Table Tools tab, Resize) and add more entries.
Also, I changed the formulas to use structured table references, so you should not have to change the formulas after resizing and changing your customer/horse lists.
I added a workbook open macro to turn on iterative calculation to save you a step (just don't save the workbook as an xlsx file as that will strip out the macros).
Oct 17 2020 09:10 PM
@JMB17 Hi again. I got it to work well when the number of buyers was equally divisable by 24. But when it was an odd number such as below I had issues.
I resized the table to 207 sweeps buyers and followed your instructions but for some reason there are some gaps from Sweeps buyers 193 - 207. These final rows remained as false.
Perhaps to make this work, I will need to sell the number of sweeps tickets that will make it divisable by 24 exactly since I am trying to replicate what happens in the real world of 'sweeps'.
You have been a terrific help to me in this so thanks. When we get closer to the date I will come back to you with how I am going with the number of sales. I have put a deadline of 12 noon on Mon 2nd Nov for me to finalise allocations then I am going to send 24 emails to the buyers advising them of their horse. The race itself takes place the following day on Tues 3rd Nov. Fun hey?
Oct 18 2020 08:03 PM
Hi again, I am putting this spreadsheet through the hoops but I think it is great.
I extended the number of buyers to 212 sweeps buyers by dragging the last few rows down but when I hold the F9 key it came up with an error to do with a circular reference. I still think the result was correct though. It might have something to do with how I am copying the rows down. I have attached the error and the spreadsheet again.
So to make it completely fair to buyers and to be truly random so it replicates drawing a horse 'out of the hat', I think the number of buyers needs to be divisable by 24, that way each horse is allocated the same number of times. If you could fix the error message I think that will fine, I have already taken up alot of your time.
Thanks again for your help, much appreciated.
Oct 18 2020 09:16 PM - edited Oct 18 2020 09:19 PM
The file contains circular references by design (otherwise, the random function would never "lock" and just keep recalculating). In order for the formulas to calculate, formula iteration must be enabled (File/Options/Formulas - Enable Iterative Calculation.
When the option is not enabled, you will get the circular reference error. The workbook contains an auto open macro to enable iterative calculation, but it doesn't run before excel attempts to calculate the formulas - so you may get the circular reference error before the auto open macro enables iterative calculation (but should be able to just click through it).
If it doesn't appear to be calculating, double check that option is enabled.
Oct 19 2020 01:21 AM
@JMB17 Many thanks for explanation. I have learnt alot during this but I shall leave such work to experts like you. Great job and thanks for being so responsive. I will give you an update when my event is imminent. You may even want to buy a sweeps ticket here https://www.trybooking.com/events/landing?eid=667369&
Oct 29 2020 09:59 PM
Guess what I am back again. I have the sweep worked out but now I need a random generator for the raffle. I thought we would have been able to do this raffle draw 'in person' but it wasn't to be so I will be doing the draw in Excel. I didn't want to play around too much with your Sweep code for fear of messing it up. But could you show me how to do a simple random selection on ticket numbers. In my sample attached I have 62 people who have purchased a raffle ticket. I exported the unique ticket numbers from excel. So I need to randomly draw out 5 names - 5th Prize, 4th Prize, 3rd Prize, 2nd Prize and then 1st Prize. Some people have purchased multiple raffle tickets. I am guessing that part of what you have already provided me will work.
I promise this is the last time I will bother you. Thankyou again ! Maree
Oct 31 2020 11:23 AM
Oct 31 2020 11:52 PM
@JMB17 Hi again. Using Random.org was a good idea. Since I am trying to replicate the drawing of winners for 5 prizes, I used the Integer generator using 5 sets (5 draws) with one unique integer. I think this replicates how a raffle draw would work the best. Probably even better because often when you do raffle draws a person can win more than once. Could you confirm my thinking is correct. Very useful having someone to bounce things off.
And with the horse program, I am doing 4 sets of 24 horses (96 tickets) as this is how a sweep would work in the physical world.
Once again, many many thanks for your help. I have learnt so much...Maree