# Random weighted sample without repetition Redux

#### KevinMcC

##### New Member
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.

### Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.

#### KevinMcC

##### New Member
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

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

Replies
5
Views
124
Replies
3
Views
971
Replies
1
Views
206
Replies
2
Views
88
Replies
2
Views
119

1,186,112
Messages
5,955,900
Members
438,225
Latest member
rsur

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back