Weighted lottery help?

[taysomraven]

I am trying to setup a spreadsheet that will allow me conduct and offline weighted lottery. The lottery operates as follows: 14 balls labeled 1 through 14 go into a lotto machine, and then 4 balls are drawn consecutively *without* replacing the balls between each draw. The resulting 4 balls make up a Combination (ie. order doesn't matter.) Using 14 possible balls, and drawing 4 at a time, that means we're working with a total of 1,001 possible combinations (written the high school math way, 14C4 = 1,001). I've used a combination generator to produce all of the 1,001 combinations, which I can copy / paste into a column in a spreadsheet.

Here's what I need help with:

1. Once the combos are in the spreadsheet, I want to assign them randomly AND in a weighed fashion to each of 4 possible people (Randy, Jake, Kyle, and Ted.) For the purpose of this example, we'll use the following names and weights:
   • Randy gets 50% of the combos
   • Jake gets 25%
   • Kyle gets 15%
   • Ted gets 10%
2. The first combo will be drawn (4 balls) and I'll match the combo up to the assigned numbers to find who won (either Ctrl+F, or something more elegant like Index / Match.)
3. The person who won will be removed from the pool of names, and then the weightings will be updated accordingly.
   • For example, if Randy's number gets drawn, then the new weighting would be determined as follows: Jake gets 50% (25/50), Kyle gets 30% (15/50), and Ted gets 20% (10/50). If it's not clear, 25+15+10 = 50, which is the denominator, and the weight the person started with is the numerator.
4. The 1,001 numbers are now assigned again using the updated weightings.
5. Now we just repeat steps 2 through 4 until everyone has been chosen.

So the biggest things I need from the workbook are:
> Randomly assign the combinations to the participants, based on the entered weightings.

> An easy way to remove a participant, recalculate the weightings, and then reassign the numbers after each draw. I'll probably be dealing with at least 10 participants, and the weightings would of course be far different too at that point. A more elegant way of removing participants, and recalculating weightings would be really wonderful (manually is certainly an option, but it's far from ideal.)


Any help is greatly appreciated!

 

[Joe4]

You're saying that the way "sum" is being used in this code isn't standard? I assumed it was a term that VBA naturally recognizes - is that not the case? I guess the part that has me confused is there's also and "rsum" in the code, which would lead me to believe that it is in fact a recognized function in VBA. If it isn't, I supposed I could comb through and find all instances of "sum" and replace it with "bagofchips" or whatever random string I come up with. Do you suppose that would help?

EDIT: I just went through and changed all instances of "sum" to "AddUp", saved the workbook with a separate name, and tried the macro again. It still gives the same error, and points to the exact same line of VBA code when I click 'Debug'.

"sum" is being used as a variable in your code. It is not a good idea to choose names for your variables that are existing names of built-in things, like the names of existing Excel functions (like "Sum").
It is not guaranteed to cause problems, but it certainly can, if VBA cannot figure out if you are trying to refer to the "Sum" function or "Sum" variable (depending on the circumstances, sometimes VBA can figure it out, in other cases, maybe not). It is best not to tempt fate and leave it to chance.

Best practices for programming say not to do that. Speaking of variables, another "best practice" is to turn on Option Explicit to force yourself to declare all variables.
This can aid in code debugging and help identify typos and prevent certain kinds of errors.
See: How to use Option Explicit Statement in VBA

Even if this is not what is causing your problem, it is good advice for programming in VBA, to help prevent unwanted errors and unexpected results.
(And then it is one more thing you can "rule out" when trying to debug your code).

 

[taysomraven]

Understood, I'll try to keep that in mind as I dip my toes into VBA here and there. As of right now, it's mostly Greek to me, so I'm going to sit tight and hope someone with much more experience can point me in the right direction with fixing the code up. It just strikes me as very strange the the same code that works on the original sheet would break when I enter a specific formula, and then continue to break even when I've reserved the problematic formula.

 

[taysomraven]

I poked around for a few minutes and figured it out. The VBA was throwing the error b/c for some reason it had a problem with blank cells that were generated by a formula -- this persisted even if I copied & pasted As Values Only. The 2 possible resolutions that I found (besides re-writing the VBA code) were as follows:

1. Manually delete the blanks that were entered into column J
2. Have the formulas I am using assign the value 0 (representing a 0% weighting) to the managers who've already won a spot

I went with option 2, as it allows the zeroes to just flow right into column J without any further manual manipulation.

==============================================================

Final Sheet:

Draft Lottery - Final - Copy.xlsm

drive.google.com

THE GUIDE:

Step 0: (This sheet doesn't assist with this part.) Determine the Original Weightings you'll start with, and enter those numbers accordingly, then enter the Manager names, and Original Weights in columns M and P, respectively.
Personally the way I picked the weightings for our league was as follows. The result is that each team has a standardized percentage chance increase over the next lowest team. Feel free to use a different way to set your weightings, but I struggled with this methodology, so I figured I'd provide it.

A11=SUM(A1:A10)
A10 = 1
B10 = best guess (start with 0.65 for example)
A9 = A10 / $B$10
Drag formula in A9 up all the way to A1
Tweak the factor in B10, til the summation in A11 is as close to 100 as possible.

Obviously if you have a different number of managers, you'll need to adjust how many rows you utilize for this. For example, if you're in a 14 person league, you'll be using A15 to sum, A14 = 1, and B=14 (or wherever you please) as the factor.

Step 1: Download and open the Excel workbook in Office 365 (older versions won't work, as I used some newer formulas.) Click Enable Content up at the top to enable the buttons (Reset and Generate.)

Step 2: Copy the Randomizer Columns (S - T), and Paste **As Values Only** into columns I - J (labeled Name and Weight % in the Weightings Table.)

Step 3: Click the Reset button to clear the Combos Table, and say "yes" to the popup.

Step 4: Click the Generate button to populate the Combos Table again. (NOTE: This uses columns I - J, so make sure you did Step 3 properly.)

Step 5: Draw 4 numbers, and do NOT replace the numbers until 4 are removed (you don't want duplicate numbers.) Record the 4 numbers Manual Draw Input Table as Balls 1 - 4. The winner's name will appear in the same row as the entries, and you should get a warning showing next to the name, indicating it's still a formula.

Step 6: Copy / Paste the winner's name (only the one you just drew) onto itself **As Value Only.** Make sure you're doing it As Value Only to kill the formula, otherwise it'll continue to change the winner name as you do more draws!!

Step 7: Repeat steps 2 - 6 until all names are drawn.


NOTE: If you want to understand what all the columns / cells are for, look for the Notes I left describing them. For the important ones, I tried to explain what they are, why they exist, and what you need to do with them (if anything.)

UNRESOLVED ISSUES: For some reason the Combos Table still populates 1001 names rather than 1000, despite it working off of percentages that *should* tell it that only 1000 names are necessary. You'll recall that drawing 11, 12, 13, 14 equates to a re-draw, so it isn't a major issue -- just pay attention as you're drawing, and if you get a re-draw, just overwrite the winner name with REDRAW and then keep going.

 

[taysomraven]

I hope I'm not going to get anyone mad, but I wouldn't have been able to do this without help from bebo021999, so I marked that one as the solution. I did end up developing the rest of the sheet further (and plan on probably implementing additional macros to take care of some of the other steps) but I really, really appreciate the help. Thank you!!

 

[bebo021999]

I'm glad to hear that it worked. I suppose the current final file requires a lot of manual steps, and I'm willing to assist in completing it if you can provide more detailed explanations about the table on the upper right side, from columns M to Q. Furthermore, since I don't have Excel 365, please manually enter the results and notes for cells that use it
Good luck!