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 werenotselected, 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.