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

#### aghaffar82

##### Board Regular
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

### Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"

#### KRice

##### Well-known Member
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
ABCDEF
1Value appearingredblue
2% of cases75%25%
3Case #Value
41blue
52red
63blue
74red
85red
96red
107red
118red
129red
1310blue
Sheet2
Cell Formulas
RangeFormula
F2F2=1-E2
B4:B13B4=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

##### Board Regular
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

##### Well-known Member
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
ABCDEFG
1Count blue25Value appearingredblue
2Count red75% of cases75%25%
3Case #ValueRnd()Case #
41red0.5073441red
52blue0.0550862red
63red0.6067623red
74blue0.07554red
85red0.8074955red
96red0.6366796red
107blue0.1095717red
118red0.8180498red
129red0.8054139red
1310red0.93614210red
1411blue0.03201611red
1512blue0.08173812red
1613blue0.11315113red
1714red0.7366414red
1815blue0.05526115red
1916red0.31985116red
2017red0.50607717red
2118blue0.26610918red
2219blue0.12687319red
2320red0.4976320red
2421red0.4180521red
2522blue0.25055122red
2623red0.46080423red
2724red0.36597524red
2825red0.75886825red
2926red0.57505326red
3027red0.45221427red
3128red0.90505528red
3229red0.30531829red
3330red0.9878930red
3431red0.50862331red
3532red0.34701132red
3633red0.82404133red
3734red0.66489834red
3835red0.8680135red
3936red0.42397436red
4037red0.52160437red
4138red0.98367638red
4239red0.50885939red
4340red0.85619140red
4441red0.50804741red
4542red0.34053642red
4643red0.59104143red
4744red0.36928444red
4845red0.70562945red
4946red0.37981546red
5047red0.55658947red
5148red0.63252648red
5249red0.86674449red
5350red0.31196750red
5451blue0.26276351red
5552red0.33210352red
5653red0.43223253red
5754blue0.1326954red
5855red0.57959755red
5956blue0.18943456red
6057red0.37087157red
6158red0.30776458red
6259red0.95687159red
6360red0.79171760red
6461blue0.30073361red
6562red0.60090462red
6663blue0.22251463red
6764blue0.01414464red
6865red0.69133865red
6966red0.96233666red
7067red0.31591667red
7168red0.88161268red
7269blue0.076569red
7370blue0.22709870red
7471red0.50516371red
7572red0.97388672red
7673blue0.14824773red
7774red0.81921574red
7875red0.52087875red
7976red0.36950376blue
8077red0.64933777blue
8178red0.96744278blue
8279blue0.24297279blue
8380red0.38179380blue
8481red0.574881blue
8582red0.5391182blue
8683red0.3705483blue
8784red0.42525284blue
8885blue0.02785885blue
8986red0.569886blue
9087red0.48297787blue
9188red0.71090788blue
9289blue0.27001789blue
9390red0.57478490blue
9491red0.55020891blue
9592red0.79308792blue
9693blue0.02144493blue
9794red0.99064794blue
9895blue0.29619295blue
9996red0.32025396blue
10097red0.48561797blue
10198blue0.18256298blue
10299red0.40460699blue
103100red0.429971100blue
Sheet3
Cell Formulas
RangeFormula
B1B1=COUNTIF(\$B\$4:\$B\$103,"blue")
B2B2=COUNTIF(\$B\$4:\$B\$103,"red")
G2G2=1-F2
B4:B103B4=INDEX(\$F\$4:\$F\$103,RANK.EQ(C4,\$C\$4:\$C\$103)+COUNTIF(\$C\$4:C4,C4)-1,1)
C4:C103C4=RAND()
F4:F103F4=IF(E4<=\$F\$2*MAX(\$E\$4:\$E\$103),\$F\$1,\$G\$1)

#### aghaffar82

##### Board Regular

ADVERTISEMENT

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

#### KRice

##### Well-known Member
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

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

### Similar threads

Replies
1
Views
192
Replies
4
Views
255
Replies
4
Views
236
Replies
6
Views
101
Replies
3
Views
450

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Threads
1,163,951
Messages
5,834,548
Members
430,295
Latest member
amdis

### 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

### 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