Randbetween no duplicates with unequal probabilities (draw simulation)

[mrfahaji]

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:

Value	ID	Club
0	1	Club A
20	2	Club B
40	3	Club C
50	4	Club D
60	5	Club E
70	6	Club F
75	7	Club G
80	8	Club H
82	9	Club I
83	10	Club J
…..	…..	…..
631	136	Club 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).

 

[mrfahaji]

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!

 

[KRice]

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