How to generate random value based on assigned probability in Excel?
If there is a table with some values and corresponding assigned percentages as below screenshot shown in a sheet. And now, I want to generate random values based on the list of values and their assigned probabilities.
Recommended Productivity Tools for Excel
Office Tab: Bring powerful tabs to Office (include Excel), just like Chrome, Safari, Firefox and Internet Explorer. Save you half the time, and reduce thousands of mouse clicks for you. 30-day Unlimited Free Trial
Kutools for Excel: Save 71% of your time and solve 82% Excel problems for you. 300+ advanced tools designed for 1500+ work scenario, make Excel much easy and increase productivity immediately.60-day Unlimited Free Trial
In fact, to generate random values with the probability, you only need two formulas.
1. In adjacent cell of the table, type this formula =SUM($B$2:B2), and drag this formula down to the cells you need. See screenshot:
2. Select a blank cell which you will place the random value at, type this formula =INDEX(A$2:A$8,COUNTIF(C$2:C$8,"<="&RAND())+1), press Enter key. And press F9 key to refresh the value as you need.
- How to generate random number without duplicates in Excel?
- How to keep/stop random numbers from changing in Excel?
- How to generate random Yes or No in Excel?
|Here the Insert Random Data of Kutools for Excel can help you quickly insert random unique integer, date, or random time, text string, custom list as you need.|
Recommended Productivity Tools
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
To post as a guest, your comment is unpublished.· 7 months agoSorry Just Noticed that I extend the cumulative prob. outside the desired limits
To post as a guest, your comment is unpublished.· 7 months agoDoes this work if the data in the cells are horizontal instead of vertical? Mine is not. Thanks
To post as a guest, your comment is unpublished.· 7 months agoHello, can anyone help me getting the correct formula? I would like to get random values but with certain limits. For example, randomly get "red, blue, green, orange or pink", but I have a specific quantity of items for each color so I have to set this condition within the formula.
To post as a guest, your comment is unpublished.· 6 months agoYou can try the Insert Random Data to create the custom list as red, blue, green, orange and pink, then insert then to range randomly.
To post as a guest, your comment is unpublished.· 1 years agoWhy do the values from this function change every time I make an adjustment to the worksheet? I can't test my sample data if the results keep changing
To post as a guest, your comment is unpublished.· 1 years agoAnyone know how I would go about doing this without replacement using probability pools. Here's an example: Pool A has 10 people. Pool B has 7 People. There is an 80% probability we will pull someone from Pool A; 20% probability we will pull someone from pool B. We want to randomly pick 5 people from the 2 pools without replacement. Even without replacement, the probability of pulling from either pool stays at 80/20. Any Ideas?