Randbetween no duplicates with unequal probabilities (draw simulation)

mrfahaji

New Member
Joined
Oct 25, 2012
Messages
34
Hi,

I have created a list of 136 clubs, and I want to select 40 of them at random. However, they do not have the same probability of being selected (they are five ‘tiers’, with the better clubs more likely to be chosen). The process should simulate a draw, where if the first number revealed belongs to Club A, then Club A go in that slot and cannot be selected again.

In order to simulate this, I have mapped ranges to single numbers, where the best clubs are +20 each time, the next tier +10, then +5, +2 and +1. I then use a VLOOKUP with a 1 as the [range lookup]. The RANDBETWEEN function is therefore set at (0,631) and it maps to 1-136 different clubs. Hopefully that's clear...

I run into problems because I want the first 40 non-duplicate numbers to be used. The RANK.EQ/COUNTIF does not seem to work because it ranks the entire list, and therefore the first drawn number gets altered due to the ranking process (e.g. if number 2 is drawn, then that should be 'Club B', but if there are seven “1”s further down the list, this 2 becomes an 8).

This is a shorted sample of how I have mapped the values to the clubs (note the changing intervals to reflect the different probability). Club names are illustrative only, so no trickery on the text strings:

ValueIDClub
01Club A
202Club B
403Club C
504Club D
605Club E
706Club F
757Club G
808Club H
829Club I
8310Club J
…..…..…..
631136Club ZZZ



Then my current attempt at solving this is:

RANDBETWEEN(0,631) / VLOOKUP(rand,table above,2,1) / RANK.EQ+COUNTIF

Which does give me a unique list, just not the list I want.

The main issue is that the clubs have different probability of being selected, and I need to figure a way to represent this while also not having duplicate results. Any suggestions welcome!

I don’t really want to use VBA and I’m not using 365 Apps (so no RANDARRAY, which might not solve the problem anyway).
 
A bit of both - I added a static column but for now just made it equal the volatile column. I figured at some stage it will be useful to convert the values to fixed.

Ah ok, AGGREGATE is a new function for me, sounds like it could be a useful one. Just a coincidence then that the function number was the same as the number of clubs in your example!
 
Upvote 0

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.
That’s great! AGGREGATE is one of those oft overlooked functions. it is especially useful with older versions of Excel because it handles arrays well and offers a lot of computational options: 14 as the 1st argument of AGGREGATE returns the nth largest array value, and other 1st argument codes return other results (average, etc,).
 
Upvote 0

Forum statistics

Threads
1,213,535
Messages
6,114,192
Members
448,554
Latest member
Gleisner2

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