Randomize as equally possible

[mahmed1]

Hi

I have a list or Agent Names from column A
Say first set of rows is A2:A50

I want to generate random number between 1,3 but want to make sure its eventually spread or as even as evenly possible

How can I achieve this tweaking this randbetween formula
=Randbetween(1,3)

 

[Eric W]

Do you mean equal amounts of 1s, 2s, and 3s, as close as possible? Then try:

Book1
	A	B
1	Name	Code
2	A	1
3	B	1
4	C	2
5	D	1
6	E	3
7	F	2
8	G	3
9	H	2
10	I	1
11	J	3
12	K	3
13	L	1
14	M	2
15	N	3
16	O	1
17	P	2
18	Q	2
19	R	1
20	S	2
21	T	3
22	U	2
23	V	1
24	W	3
25	X	2
26	Y	1
27	Z	3
Sheet5

Cell Formulas
Range	Formula
B2:B27	B2	=LET(a,COUNTA(A2:A50),SORTBY(INT(SEQUENCE(a,,,3/a)),RANDARRAY(a)))
Dynamic array formulas.

 

[mahmed1]

Hi Eric yes thats what i meant

 

[Eric W]

See my previous reply - I added the formula.

 

[mahmed1]

An absolute legend thank you

I need to tweak this slightly where i need to generate random numbers in the range but for some of the names i need to generate 4 to 7, 8 to 9 etc

I have a table that has the look up group value thats stored in column B

So Data
Agents, Grouping, Value
A. GroupA. 20
B Group A. 30
C. GroupB. 10
D. GroupC. 9
E. GroupB. 15

Etc

Lookup Table

Grouping, RandStart, RandEnd
GroupA. 1. 3
GroupB. 5. 7
GroupC. 9. 11

So that essentially that lookup part where the sequence has 3 needs to be replaced bu looking up the group from column B in lookuptable to see what numbers i need to equally spread for that grouping

Hoping that makes sense

 

[mahmed1]

I had a go



=LET(a, COUNTIF(A2:A50,”GroupA”),SORTBY(INT(SEQUENCE(a,,, Index(lookuptable,Match(“GroupA”, LookupTable(“C2:C7”,0))/a)),RANDARRAY(a))



I have 6 groups



Problem i have is how can i make the lookup and counta/countif part dynamic



I can do it multiple times for each group but was hoping to do in 1 formula

 

[mahmed1]

Hi Eric - so i tried to tweak formula to give diff values based on grouping so changed formula for each group but it looks like it only works for first group so not 1 to 3 and not 5 to 7, 9 to 11 etc (starting point not right)

Lookup Table

Group	Sort By	Rand Start	Rand End
0 - 3 Months	1​	1​	3​
3 - 6 Months	2​	5​	7​
6 - 12 Months	3​	9​	11​
1 - 2 Years	4​	13​	15​
2 - 4 years	5​	17​	19​
5 Years +	6​	21​	23​

Agent	Group	Value	Random Number
A1	0 - 3 Months	25.00%​	2​
A9	0 - 3 Months	25.00%​	1​
A13	0 - 3 Months	2.00%​	1​
A21	0 - 3 Months	21.00%​	2​
A25	0 - 3 Months	21.00%​	3​
A33	0 - 3 Months	27.00%​	2​
A37	0 - 3 Months	21.00%​	1​
A45	0 - 3 Months	11.00%​	3​
A2	3 - 6 Months	16.00%​	11​
A10	3 - 6 Months	21.00%​	10​
A14	3 - 6 Months	25.00%​	7​
A22	3 - 6 Months	21.00%​	5​
A26	3 - 6 Months	29.00%​	6​
A34	3 - 6 Months	7.00%​	9​
A38	3 - 6 Months	12.00%​	5​
A46	3 - 6 Months	13.00%​	8​
A3	6 - 12 Months	19.00%​	15​
A11	6 - 12 Months	2.00%​	11​
A15	6 - 12 Months	21.00%​	14​
A23	6 - 12 Months	9.00%​	13​
A27	6 - 12 Months	11.00%​	9​
A35	6 - 12 Months	8.00%​	10​
A39	6 - 12 Months	17.00%​	17​
A47	6 - 12 Months	3.00%​	18​

Formulas used for each group(First Group)

=LET(a,COUNTA(R4:R11),SORTBY(INT(SEQUENCE(a,,INDEX($H$4:$H$9,MATCH(R4,$F$4:$F$9,0)),INDEX($I$4:$I$9,MATCH(R4,$F$4:$F$9,0))/a)),RANDARRAY(a)))
Should generate numbers from 1 to 3 equally

Second Group
=LET(a,COUNTA(R12:R19),SORTBY(INT(SEQUENCE(a,,INDEX($H$4:$H$9,MATCH(R12,$F$4:$F$9,0)),INDEX($I$4:$I$9,MATCH(R12,$F$4:$F$9,0))/a)),RANDARRAY(a)))

Should generate numbers from 5 to 7 equally which it aint

Third Group Etc
=LET(a,COUNTA(R20:R27),SORTBY(INT(SEQUENCE(a,,INDEX($H$4:$H$9,MATCH(R20,$F$4:$F$9,0)),INDEX($I$4:$I$9,MATCH(R20,$F$4:$F$9,0))/a)),RANDARRAY(a)))
Should generate numbers from 9 to 1 equally which it aint

etc

 

[Eric W]

I wish you'd explained the whole problem first, this is a much trickier problem. This is about the best I could come up with:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	Agent	Grouping	Value		Grouping	RandStart	RandEnd		Random Lists
2	A	GroupA	3		GroupA	1	3		3	2	1	2	1	3
3	B	GroupB	6		GroupB	5	7		6	6	5	7	5
4	C	GroupB	6		GroupC	9	11		11	10	9
5	D	GroupB	5		GroupD	12	13		13	12	12	13
6	E	GroupA	2		GroupE	15	17		15	17	16	15	15	16	17
7	F	GroupC	11		GroupF	20	25		20
8	G	GroupD	13
9	H	GroupA	1
10	I	GroupF	20
11	J	GroupE	15
12	K	GroupC	10
13	L	GroupE	17
14	M	GroupC	9
15	N	GroupE	16
16	O	GroupE	15
17	P	GroupB	7
18	Q	GroupA	2
19	R	GroupD	12
20	S	GroupE	15
21	T	GroupE	16
22	U	GroupA	1
23	V	GroupD	12
24	W	GroupD	13
25	X	GroupA	3
26	Y	GroupE	17
27	Z	GroupB	5
Sheet6

Cell Formulas
Range	Formula
I2:N2,I7,I6:O6,I5:L5,I4:K4,I3:M3	I2	=LET(a,COUNTIF($B$2:$B$27,E2),SORTBY(INT(SEQUENCE(,a,F2,(G2-F2+1)/a)),RANDARRAY(,a)))
C2:C27	C2	=INDEX($I$2:$AA$7,MATCH(B2,$E$2:$E$7,0),COUNTIF(B$2:B2,B2))
Dynamic array formulas.

Next to your table with the groups and ranges, I added the formula to create a random, evenly distributed list for each group. You can hide this, or put it on another sheet. Then the C2 formula, which you have to drag down, just does a lookup for the value.

I tried to come up with a single cell formula, and I think it's possible, but it was giving me a headache! It would also be much easier with some of the newer functions like DROP and VSTACK, which my version of Excel doesn't have. See if this does what you want, and if so, start another thread to see if someone with the newer functions might take a shot at a single cell formula.

 

[mahmed1]

Thank you so much Eric - i really appreciate your help

where did i go wrong in the original formula if i was to use for each group so the start and end works as expected?

=LET(a,COUNTA(R4:R11),SORTBY(INT(SEQUENCE(a,,INDEX($H$4:$H$9,MATCH(R4,$F$4:$F$9,0)),INDEX($I$4:$I$9,MATCH(R4,$F$4:$F$9,0))/a)),RANDARRAY(a)))

 

[mahmed1]

I think this is where i was messing up based on your latest post

=LET(a,COUNTA(R4:R11),
SORTBY(INT(SEQUENCE(
a,,INDEX($H$4:$H$9,MATCH(R4,$F$4:$F$9,0)),(INDEX($I$4:$I$9,MATCH(R4,$F$4:$F$9,0)) - INDEX($H$4:$H$9,MATCH(R4,$F$4:$F$9,0)) + 1)/a)),
RANDARRAY(a)))