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

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 Toolsets:  12 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...

kte tab 201905

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!
Gabriel
Gabriel
This comment was minimized by the moderator on the site
A formula ta errada... <= altera a P()... Tem que ser < apenas... 

Reply
0
0
Kingsley
Kingsley
This comment was minimized by the moderator on the site
You're a life saver.
Thanks a million!
Reply
0
0
CUTO
CUTO
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 Ạ?
Reply
0
0
Stefano
Stefano
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.

Reply
0
0
KYRIACOS
KYRIACOS
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.
Reply
0
0
kyrIACOS
kyrIACOS
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.
Reply
0
0
Preston Ehrsam
Preston Ehrsam
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.
Reply
0
0
Sunny
Sunny     Preston Ehrsam
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.
Reply
Report
0
0
Chudi
Chudi
This comment was minimized by the moderator on the site
Sorry Just Noticed that I extend the cumulative prob. outside the desired limits
Reply
0
0
Chudi
Chudi
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
Reply
0
0
Maria
Maria
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!
Reply
0
0
Sunny
Sunny     Maria
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.
View Attachments (1 / 5)
Reply
Report
0
0
There are no comments posted here yet
Load More
Please leave your comments in English
Guest
Posting as Guest
×
Rate this post:
Reset
0   Characters
Suggested Locations