Random weighted sample without repetition Redux

[KevinMcC]

I found a very old post that answered my question. However, I'm having a small problem. So I'm asking for help.

I want to say this solution dcardno posted is amazing. I would have never figured it out and it does do what I need for the most part, and the best part is it does not require any VBA which was important to me as a solution. However, the formula in D105 references C4 which is empty and creates a range between 0 and 2000. If RANDBETEEN(C4,C104) generates a "0" I get "#N/A" in the D107 INDEX formula. In the scheme of things this is an infrequent, but surprisingly not a rare occurance. Is there any way to fix this?

I thought I had a larger issue where I would sometimes get a duplicate result, but it turns out I had a typo that I continually overlooked.

Thanks

Kevin

I was hoping I could post an attached file, since it is a little hard to follow the formulas, but I have a non-VBA solution:

> In Column A, put the values from 1 to 100 (mine range from Row 5 to 104, so that's what my formula examples will reference)

> In Column B, place the number of chances (balls) available to each participant: row 5 to 24 will have 45, rows 25 to 54 will have 20, etc. I put in a total for the column to make sure I came up to 2,000

> In column C put in the cumulative chances to date:
Cell C5 has the formula = C4 + B5 (copy that down to Row 104 - it should come to 2,000)

> In Column D, down below all the other entries, insert a formula to select a random number representing the particular ball (chance) selected:
Cell D105 has the formula =RANDBETWEEN(C4, C104) - this will return an integer between 0 and 2000 (in the first round - this will change in subsequent rounds)

>In column D place an indicator of whether a particular element has been selected (ie, is related to the selected ball):
Cell D5 has the formula =IF(AND(C4 < D$105, C5 >= D$105), 1, 0) (note the absolute and relative references, and copy this down to D104)

>the values in Column D now represent the (many) non-selected elements with a "0" and the (one) selected element with a "1"

> Down below the column, insert a formula to indicate which element has been selected:
Cell D107 includes a formula =INDEX($A$5:$A$104, MATCH(1, D5:D104,0))

> I like to put a blank column in between these sorts of calculations, so I left Column E blank.

>Round 2 selections are represented in columns F:H

> We need to indicate how many chances each element has in the next round: a previously-selected element has no chance at a second selection:
Cell F5 includes a formula =B5 - D5 * B5 - copy this down to F104. This returns the original number of chances for elements that were not selected, and nil for the element that was.

> The construction of the next two columns is the same as the equivalents for Round 1

> The construction of subsequent rounds is the same as for Round 2

> The values in the cells D107, H107, L107 (etc) represent the selected elements, which will change each time you recalculate the sheet.

 

[KevinMcC]

I think I finally solved my problem. My typo error contributed to my struggle.

I added a new formula in D106 - +IF(D105=0,1,D105) Then I changed the formula in D5 to reference the new formula in D106 instead of D105.

Kevin

 

[KevinMcC]

Typos and I have some issues. =IF(D105=0,1,D105)