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.
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?
Best Office Productivity Tools
Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time. Click Here to Get The Feature You Need The Most...
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!