Skip to main content

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.
doc random with probability 1

Generate random value with probability


arrow blue right bubble Generate random value with probability

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:
doc random with probability 2

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.
doc random with probability 3


Relative Articles:

Best Office Productivity Tools

🤖 Kutools AI Aide: Revolutionize data analysis based on: Intelligent Execution   |  Generate Code  |  Create Custom Formulas  |  Analyze Data and Generate Charts  |  Invoke Kutools Functions
Popular Features: Find, Highlight or Identify Duplicates   |  Delete Blank Rows   |  Combine Columns or Cells without Losing Data   |   Round without Formula ...
Super Lookup: Multiple Criteria VLookup    Multiple Value VLookup  |   VLookup Across Multiple Sheets   |   Fuzzy Lookup ....
Advanced Drop-down List: Quickly Create Drop Down List   |  Dependent Drop Down List   |  Multi-select Drop Down List ....
Column Manager: Add a Specific Number of Columns  |  Move Columns  |  Toggle Visibility Status of Hidden Columns  |  Compare Ranges & Columns ...
Featured Features: Grid Focus   |  Design View   |   Big Formula Bar    Workbook & Sheet Manager   |  Resource Library (Auto Text)   |  Date Picker   |  Combine Worksheets   |  Encrypt/Decrypt Cells    Send Emails by List   |  Super Filter   |   Special Filter (filter bold/italic/strikethrough...) ...
Top 15 Toolsets12 Text Tools (Add Text, Remove Characters, ...)   |   50+ Chart Types (Gantt Chart, ...)   |   40+ Practical Formulas (Calculate age based on birthday, ...)   |   19 Insertion Tools (Insert QR Code, Insert Picture from Path, ...)   |   12 Conversion Tools (Numbers to Words, Currency Conversion, ...)   |   7 Merge & Split Tools (Advanced Combine Rows, Split Cells, ...)   |   ... and more

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

Description


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!
Comments (19)
No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
A formula ta errada... <= altera a P()... Tem que ser < apenas... 

This comment was minimized by the moderator on the site
You're a life saver.
Thanks a million!
This comment was minimized by the moderator on the site
MÌNH KHÔNG HIỂU LẮM. TẠI SAO PHẢI LÀM NHƯ VẬY Ạ?
This comment was minimized by the moderator on the site
Ottimo lavoro! Questo è proprio quello che cercavo. Però non funziona con Calc di Open Office, che fra l'altro accetta solo i comandi in italiano.
E' possibile tradurla in modo che funzioni anche con calc? Io non ci sono riuscito.Grazie.

This comment was minimized by the moderator on the site
THE FORMULA WORKS ONLY IF ONE SUBSTITUTES RAND() WITH RANDBETWEEN(1;X) WHERE X IS THE TOTAL NUMBER OF POSSIBLE OUTCOMES, WHICH WOULD SIMPLY BY AN INTEGER GREATER THAN 1.
This comment was minimized by the moderator on the site
frankly I don't think this formula works.
I am trying to sample the results of an election so I have in a row the votes of different parties. no I want to go in & pick up randomly ten ballots (sort of like exit polling works), but I always get the first party in the rows.
This comment was minimized by the moderator on the site
How can I utilize this random number generator but only have it generate odd or even numbers inside of the criteria? When I add the "ODD" or "EVEN" coding, it only produces the number "1" in the cell.
This comment was minimized by the moderator on the site
Hi, Preston Ehrsam, the Insert Random Data tool cannot insert random even or odd numbers only.
This comment was minimized by the moderator on the site
Sorry Just Noticed that I extend the cumulative prob. outside the desired limits
This comment was minimized by the moderator on the site
Does this work if the data in the cells are horizontal instead of vertical? Mine is not. Thanks
This comment was minimized by the moderator on the site
Hello, 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.
Thanks!
This comment was minimized by the moderator on the site
You can try the Insert Random Data to create the custom list as red, blue, green, orange and pink, then insert then to range randomly.
There are no comments posted here yet
Load More
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations