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

aghaffar82

Board Regular
Joined
Jun 13, 2019
Messages
65
Office Version
  1. 365
Platform
  1. Windows
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
 

Some videos you may like

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.

KRice

Well-known Member
Joined
Dec 9, 2003
Messages
965
Office Version
  1. 2019
Platform
  1. Windows
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
Joined
Jun 13, 2019
Messages
65
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Dec 9, 2003
Messages
965
Office Version
  1. 2019
Platform
  1. Windows
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
Joined
Jun 13, 2019
Messages
65
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

KRice

Well-known Member
Joined
Dec 9, 2003
Messages
965
Office Version
  1. 2019
Platform
  1. Windows
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
Joined
Jun 13, 2019
Messages
65
Office Version
  1. 365
Platform
  1. Windows
Thank you for the tip, I wasn't aware of F9 but knew that with every calculation Rand changes.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,143
Messages
5,599,975
Members
414,354
Latest member
Flaxarn

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

Which adblocker are you using?

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
Top