Random weighted sample without repetition Redux

KevinMcC

New Member
Joined
May 21, 2021
Messages
5
Office Version
  1. 365
Platform
  1. Windows
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

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.
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
 
Upvote 0
Solution

Forum statistics

Threads
1,212,933
Messages
6,110,752
Members
448,295
Latest member
Uzair Tahir Khan

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top