Forum Discussion
Random selection for raffle winners
- With your permission, if I can recommend you, add a file (without sensitive data) to your project.
Explain your plans in relation to this file.
So you can get a solution that is tailored to your needs much faster.
At the same time, it is much easier for someone who wants to help to understand the subject.
A win-win situation for everyone.
Here is a VBA suggestion.
The list of names in column A can be of any length, but must begin in A1.
The second macro builds in the button to trigger the first macro.
Code:
Sub raffle ()
Dim rngBer As Range
Dim lngZ As Long
Randomize
lngZ = Cells (Rows.Count, 1) .End (xlUp) .Row
Set rngBer = Range (Cells (1, 1), Cells (lngZ, 1))
lngZ = CLng (Int (lngZ * Rnd + 1))
Cells (lngZ, 1) .Offset (0, 1) .Value = Cells (lngZ, 1) .Offset (0, 1) .Value + 1
End Sub
'************************************************* *
Sub button ()
With ActiveSheet.Buttons.Add (243, 24, 104.25, 45)
.OnAction = "Raffle"
.Characters.Text = "Start"
End With
End Sub
If you click the button with the right mouse button, you can move it to any position.I would be happy to know if I could help.
Nikolino
I know I don't know anything (Socrates)
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.
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
- I'm glad you have it sorted out and good luck with your event!
JMB17 Thankyou for being so quick to respond.
Each ticket is unique and a person can buy multiple tickets.
Therefore 1 set of 5 numbers is the way to go.
JMB you have been wonderful. Not sure if against the rules but can you tell me your first name. If so I can publicly recognise you for your input. I don't even know if you are in Australia, but if you are, you will know about the Melbourne Cup.
Regards
Maree
- I think the question is if one person can win more than once with the *same* ticket versus winning more than once on a different ticket.
If you were drawing numbers out of a hat, are you putting the number that was drawn back in the hat?
If they can win more than once on the same ticket, then I think 5 sets of 1 number would be correct. If they cannot win more than once on the same ticket, then I think 1 set of 5 unique numbers would be correct. 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
- Sorry for the late reply, I've not been on here this week. For a raffle drawing, I would suggest using random.org. They have a random number generator on their home page that is free - number your list 1 to 62 and use that for the min/max for the random number generator.
If the same number cannot win more than once, then scroll down to the "Numbers" section of the home page for the integer set generator, which you can use to generate a set of 5 unique numbers between 1 and 62.