Formula to get random values (text) based on inserted percentage

[aghaffar82]

Hello, I have two values i.e. "red" & "blue" and I want to list (100 entries) them randomly and also want to make sure that they show only a certain percentage inserted in cell E2. The cell E1 would be either red or blue. For example, if % entered in cell E2 is 75% and value in cell E1 is red, it should list randomly (red, red, blue, red.....) 75 times "red" and 25 times "blue" in the cells A3:A102.

Thank you and looking forward to your kind help. Best Regards

 

[KRice]

You mention that you'd like the values selected randomly. The easy approach is to target a 75/25 distribution using the RAND function, as shown in this example (extend the cases and formula for 100), although this may not be what you want.

Book1
	A	B	C	D	E	F
1				Value appearing	red	blue
2				% of cases	75%	25%
3	Case #	Value
4	1	blue
5	2	red
6	3	blue
7	4	red
8	5	red
9	6	red
10	7	red
11	8	red
12	9	red
13	10	blue
Sheet2

Cell Formulas
Range	Formula
F2	F2	=1-E2
B4:B13	B4	=IF(RAND()<$E$2,$E$1,$F$1)

This will not necessarily give a 75/25 distribution for every recalculation, but will, on average give a 75/25 distribution over many recalculations. Are you looking for a firm constraint of 75/25 every time? If so, then a different approach is necessary...probably involving the creation of an entire list consisting of all of the values (i.e., 75 reds and 25 blues), and then one is selected randomly and added to the list being constructed (and then that item is no longer available for subsequent random selections).

 

[aghaffar82]

Thank you so much for your response, it is quite close, I enter the percentage in cell E2 and things work as desired but the outcome is not accurate as you already indicated. I need exact random number of occurrences as per the inserted percentage in cell E2.

 

[KRice]

I suspected you wanted the other approach. This is more cumbersome, but it makes a random selection from a list, without replacing any selected values. The initial list of values to select from is built in columns E and F, depending on inputs in F1:G2. Make sure this list of values is long enough...currently 100 cases long. Then in column C, a helper formula generates random values which are then ranked so that a randomly selected value is pulled from the list in column F. I've added some COUNTIF formulas in B1:B2 as a quick confirmation.

Book1
	A	B	C	D	E	F	G
1	Count blue	25			Value appearing	red	blue
2	Count red	75			% of cases	75%	25%
3	Case #	Value	Rnd()		Case #
4	1	red	0.507344		1	red
5	2	blue	0.055086		2	red
6	3	red	0.606762		3	red
7	4	blue	0.0755		4	red
8	5	red	0.807495		5	red
9	6	red	0.636679		6	red
10	7	blue	0.109571		7	red
11	8	red	0.818049		8	red
12	9	red	0.805413		9	red
13	10	red	0.936142		10	red
14	11	blue	0.032016		11	red
15	12	blue	0.081738		12	red
16	13	blue	0.113151		13	red
17	14	red	0.73664		14	red
18	15	blue	0.055261		15	red
19	16	red	0.319851		16	red
20	17	red	0.506077		17	red
21	18	blue	0.266109		18	red
22	19	blue	0.126873		19	red
23	20	red	0.49763		20	red
24	21	red	0.41805		21	red
25	22	blue	0.250551		22	red
26	23	red	0.460804		23	red
27	24	red	0.365975		24	red
28	25	red	0.758868		25	red
29	26	red	0.575053		26	red
30	27	red	0.452214		27	red
31	28	red	0.905055		28	red
32	29	red	0.305318		29	red
33	30	red	0.98789		30	red
34	31	red	0.508623		31	red
35	32	red	0.347011		32	red
36	33	red	0.824041		33	red
37	34	red	0.664898		34	red
38	35	red	0.86801		35	red
39	36	red	0.423974		36	red
40	37	red	0.521604		37	red
41	38	red	0.983676		38	red
42	39	red	0.508859		39	red
43	40	red	0.856191		40	red
44	41	red	0.508047		41	red
45	42	red	0.340536		42	red
46	43	red	0.591041		43	red
47	44	red	0.369284		44	red
48	45	red	0.705629		45	red
49	46	red	0.379815		46	red
50	47	red	0.556589		47	red
51	48	red	0.632526		48	red
52	49	red	0.866744		49	red
53	50	red	0.311967		50	red
54	51	blue	0.262763		51	red
55	52	red	0.332103		52	red
56	53	red	0.432232		53	red
57	54	blue	0.13269		54	red
58	55	red	0.579597		55	red
59	56	blue	0.189434		56	red
60	57	red	0.370871		57	red
61	58	red	0.307764		58	red
62	59	red	0.956871		59	red
63	60	red	0.791717		60	red
64	61	blue	0.300733		61	red
65	62	red	0.600904		62	red
66	63	blue	0.222514		63	red
67	64	blue	0.014144		64	red
68	65	red	0.691338		65	red
69	66	red	0.962336		66	red
70	67	red	0.315916		67	red
71	68	red	0.881612		68	red
72	69	blue	0.0765		69	red
73	70	blue	0.227098		70	red
74	71	red	0.505163		71	red
75	72	red	0.973886		72	red
76	73	blue	0.148247		73	red
77	74	red	0.819215		74	red
78	75	red	0.520878		75	red
79	76	red	0.369503		76	blue
80	77	red	0.649337		77	blue
81	78	red	0.967442		78	blue
82	79	blue	0.242972		79	blue
83	80	red	0.381793		80	blue
84	81	red	0.5748		81	blue
85	82	red	0.53911		82	blue
86	83	red	0.37054		83	blue
87	84	red	0.425252		84	blue
88	85	blue	0.027858		85	blue
89	86	red	0.5698		86	blue
90	87	red	0.482977		87	blue
91	88	red	0.710907		88	blue
92	89	blue	0.270017		89	blue
93	90	red	0.574784		90	blue
94	91	red	0.550208		91	blue
95	92	red	0.793087		92	blue
96	93	blue	0.021444		93	blue
97	94	red	0.990647		94	blue
98	95	blue	0.296192		95	blue
99	96	red	0.320253		96	blue
100	97	red	0.485617		97	blue
101	98	blue	0.182562		98	blue
102	99	red	0.404606		99	blue
103	100	red	0.429971		100	blue
Sheet3

Cell Formulas
Range	Formula
B1	B1	=COUNTIF($B$4:$B$103,"blue")
B2	B2	=COUNTIF($B$4:$B$103,"red")
G2	G2	=1-F2
B4:B103	B4	=INDEX($F$4:$F$103,RANK.EQ(C4,$C$4:$C$103)+COUNTIF($C$4:C4,C4)-1,1)
C4:C103	C4	=RAND()
F4:F103	F4	=IF(E4<=$F$2*MAX($E$4:$E$103),$F$1,$G$1)

 

[aghaffar82]

Thank you so much, you have been very kind and it works like magic.

 

[KRice]

You're welcome...I'm happy to help. I forgot to mention it, and you may already be aware...but you can generate a new selection by hitting F9 to force a recalculation of the RAND functions.

 

[aghaffar82]

Thank you for the tip, I wasn't aware of F9 but knew that with every calculation Rand changes.