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

 

[KRice]

If I'm following you correctly, would the following work? Use your intervals of 20, 10, etc. to establish the lower value of some arbitrary scale (column A). The end of that scale needs to be specified (see A12 where I've essentially declared that Club J also has an interval of 1). So the entire scale runs from [0,84) in this case. We compute the Interval between each subsequent club's lower range (see column D). The range [0,84) is then mapped to [0,1) (see column E) so that a uniformly distributed random number (generated with RAND()) can be used. RAND() produces numbers on the interval [0,1) and it is highly unlikely that any two will be exactly the same. We then generate uniformly distributed random numbers for however many slots we'd like to fill...say 5 in this case (see column H) and use VLOOKUP to find the corresponding club whose interval the random number falls within.

MrExcel_20220808.xlsx
	A	B	C	D	E	F	G	H	I
1	Lower Value	ID	Club	Interval	RAND lower threshold			RAND	1st 5
2	0	1	Club A	20	0	Club A		0.443997	Club B
3	20	2	Club B	20	0.238095238	Club B		0.991945	Club J
4	40	3	Club C	10	0.476190476	Club C		0.602342	Club D
5	50	4	Club D	10	0.595238095	Club D		0.058422	Club A
6	60	5	Club E	10	0.714285714	Club E		0.664705	Club D
7	70	6	Club F	5	0.833333333	Club F
8	75	7	Club G	5	0.892857143	Club G
9	80	8	Club H	2	0.952380952	Club H
10	82	9	Club I	1	0.976190476	Club I
11	83	10	Club J	1	0.988095238	Club J
12	84				1
Sheet6

Cell Formulas
Range	Formula
H2:H6	H2	=RAND()
I2:I6	I2	=VLOOKUP(H2,$E$2:$F$11,2)
D2:D11	D2	=A3-A2
F2:F11	F2	=C2
E2:E12	E2	=A2/$A$12

 

[mrfahaji]

Thanks for looking at this. Currently on phone so need to check properly tomorrow. Only question - your list of 5 is returning two Club Ds, so not sure how that is avoiding the duplicates?

 

[KRice]

Oh…sorry, I misread your post. I have no scheme in place to prevent duplicate clubs. I’ll give this some more thought.

 

[KRice]

For an older version of Excel and no VBA, you are probably going to need a solution with some helper columns. The approach I used begins with your input Source Data (columns A:D). Be sure to add the last value to this source table (shown in yellow) to indicate what would be the next lower value if another item were added to the list. This ensures that the correct interval (see orange) can be calculated for the last club shown.

Next, a lookup table (cols F:G) is created to show how the range of [0,1) is divided into subranges that correspond to the interval weighting indicated by column D.

Next, a long list of uniformly distributed random numbers between [0,1) is created (col K). This list is volatile and will recompute as the worksheet is refreshed. To avoid an ever-changing set of random numbers, I would recommend forcing a recalculation (hit F9), then copy the list of values in col K and paste them as values into column L. This list of random numbers needs to be very long compared to the number of clubs you want in your final list.

Next, VLOOKUP is used to assign whichever club corresponds to the static random numbers (see col M). Then a logic test is performed to determine if the teams shown in column M have already been selected above (see col N).

Finally, the selection order with no repeats shown in column N is consolidated in column I, where the final results are shown. I've pulled the formula down until an error appears. The error means that the bottom of column N has been reached and there are no more teams to show. You will see that not all of the teams are listed in col I, but if the random number lists in cols K:L were extended further, and the formulas in cols M:N also extended, then eventually all teams would appear in col I as the col I formula is pulled down.

To keep this mini-sheet of manageable size, I haven't shown the entire extent of cols K:N...

MrExcel_20220808.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	Source Data					Lookup for weighted RAND
2	Lower Value	ID	Club	Interval		RAND lower threshold			Weighted Selection		volatile RAND()	static RAND()	Corresponding Club	Selection Order, No Repeats
3	0	1	Club A	20		0	Club A		Club C		0.470200718	0.561150983	Club C	Club C
4	20	2	Club B	20		0.224719101	Club B		Club A		0.314998555	0.096074926	Club A	Club A
5	40	3	Club C	10		0.449438202	Club C		Club D		0.93695689	0.218719519	Club A
6	50	4	Club D	10		0.561797753	Club D		Club I		0.736602516	0.551494472	Club C
7	60	5	Club E	10		0.674157303	Club E		Club B		0.579967787	0.66598016	Club D	Club D
8	70	6	Club F	5		0.786516854	Club F		Club E		0.980922606	0.515384199	Club C
9	75	7	Club G	5		0.842696629	Club G		Club F		0.503907622	0.655208907	Club D
10	80	8	Club H	2		0.898876404	Club H		Club G		0.017246485	0.929281476	Club I	Club I
11	82	9	Club I	1		0.921348315	Club I		Club H		0.053048674	0.237126244	Club B	Club B
12	83	10	Club J	1		0.93258427	Club J		Club L		0.159156717	0.459393571	Club C
13	84	11	Club K	1		0.943820225	Club K		Club J		0.71236296	0.686114853	Club E	Club E
14	85	12	Club L	1		0.95505618	Club L		#NUM!		0.73059947	0.832354293	Club F	Club F
15	86	13	Club M	1		0.966292135	Club M		#NUM!		0.102537491	0.015826487	Club A
16	87	14	Club N	1		0.97752809	Club N				0.600961254	0.062909421	Club A
17	88	15	Club O	1		0.988764045	Club O				0.9850398	0.661208834	Club D
18	89					1					0.140067767	0.894328505	Club G	Club G
19											0.028536473	0.712825756	Club E
20											0.250260193	0.90831293	Club H	Club H
21											0.530586175	0.265428971	Club B
Sheet6

Cell Formulas
Range	Formula
M3:M21	M3	=VLOOKUP(L3,$F$3:$G$17,2)
N3:N21	N3	=IF(COUNTIF(N$2:N2,M3)>0,"",M3)
I3:I15	I3	=INDEX($M$3:$M$100,AGGREGATE(15,6,(ROW($N$3:$N$100)-ROW($N$3)+1)/($N$3:$N$100<>""),ROWS(I$3:I3)))
D3:D17	D3	=A4-A3
G3:G17	G3	=C3
F3:F18	F3	=A3/$A$18
K3:K21	K3	=RAND()

 

[mrfahaji]

Helper columns are fine! Once again I’m reading this from a phone And won’t be able to try it until later in the week, but the illustration you’ve presented looks very promising, so thank you! Will report back once tested.

 

[KRice]

Let me know if it works okay. If you want to avoid the extra step of copying the volatile random numbers to static values (the columns K:L step described in my last post), then change the M3 (and down) formula to:

=VLOOKUP(K3,$F$3:$G$17,2)

I did that in some tests and it seems to behave okay, but the list will change with every refresh of the worksheet. In other words, if you use the quicker version that directly utilizes the volatile random numbers, the resulting list of teams in column I can be examined, but if any operations are performed, the list will change. You can copy, say, the first 6 teams in column I and paste them as values elsewhere (to preserve them), but the very act of "paste as values" will cause the dynamic list in column I to change. You can try out the two variants to see which works best for your application, as it involves changing only one reference in the column M formula.

 

[mrfahaji]

Ok cool - the way that I want the sheet to run means constantly changing values is fine!

 

[mrfahaji]

This is amazing, thank you so much, it seems to work a treat! I've extended the K:N section down to 300 rows to (almost) guarantee that a unique list of 40 teams will always be generated. What do the '15,6' values in the aggregate function represent? I assumed 15 was to do with the number of clubs to select, but when I changed it to 40 it stopped working, and it seems to be fine with 15 so have left it as it is!

Thanks again, hopefully it was an enjoyable/satisfying problem to solve - I feel somewhat vindicated in asking, because it WAS solvable but clearly not straightforward!

 

[KRice]

I’m glad to hear it’s working for you. The 15 tells AGGREGATE to return the nth smallest value in the array. The “n” is the 4th argument of AGGREGATE, consisting of the ROWS term…so as the formula is pulled down, ROWS returns values of 1 then 2 then 3 and so on. The “array” that AGGREGATE operates on is the 3rd argument and is an array formed by dividing one array by another array. The numerator array contains row indexes {1;2;3;…}, and the denominator array contains the logic to consider only those rows that are not blank. This strategy is used to isolate row indexes of interest while producing “division by 0” errors everywhere else (wherever blanks appear, the logic test results in FALSE, which is coerced to a 0 during the division operation, producing an error). We want these errors because they can be easily ignored by using the “6” code as the 2nd argument of AGGREGATE. The result is a consolidated list of row indexes that are wanted, with no blanks between them. Those row indexes are then fed into the INDEX function to return the team name.

Did you use the version that uses the static RAND values or the dynamic/volatile values? If you’re using the volatile values, the static value helper column can be deleted.