How to generate random value based on assigned probability in Excel?
When working with Excel, you may occasionally need to generate random values that reflect specific underlying probabilities. For example, suppose you have a table listing several possible outcomes along with their associated probabilities, much like in the screenshot below.
This scenario is common for business simulations, project modeling, and educational purposes, where you want the random selection to accurately represent the likelihood or frequency dictated by your data.
Typical needs and use cases include:
- Simulating survey responses or customer choices, where certain answers are more likely.
- Generating test datasets or randomized draws for teaching probability concepts.
- Automating selection processes where each option has a known chance.
- Game design and risk analysis where outcomes need specific probability distributions.
Below you'll find several methods to generate random values based on assigned probabilities in Excel, including standard formula-based techniques, an advanced VBA automation, and use of Excel's built-in Data Analysis Toolpak.
➤ VBA: Generate random values with assigned probabilities
Generate random value with probability
Excel provides an accessible formula-based solution for generating random values according to specified probabilities. This method is suitable for quick tasks, works entirely within worksheets, and requires no special setup.
Before starting, make sure your values are listed in one column (A2:A8) and their corresponding probabilities (expressed as decimals between0 and1) are in the next column (B2:B8). Probabilities should sum to1 for accuracy. This solution is ideal for tables with a manageable number of values.
1. In an adjacent column (beginning at C2), enter the following formula to calculate cumulative probabilities:
=SUM($B$2:B2)Then drag this formula down to cover all your values. This creates cumulative ranges for each value, which help map a random number to a specific outcome.
2. In any blank cell (for example, D2), enter the below formula to return a random value based on your probability distribution:
=INDEX(A$2:A$8,COUNTIF(C$2:C$8,"<="&RAND())+1)Press Enter to display a random value. Every time you press F9 (to recalculate), or if worksheet data changes, a new result will appear.
Tips & Precautions:
- Probabilities in column B should add up exactly to1 (or100% if using percentages, but convert to decimals for formulas) to ensure fair distribution.
- This technique is best for short lists. If you have dozens or hundreds of values, performance may slow and maintenance becomes difficult.
- If you need to repeat the random selection for multiple draws (such as creating a batch of simulated results), simply copy the final formula to a range below or beside.
- Be careful with blank rows and mismatched ranges, as these may cause errors or unexpected results.
Error reminder: If you get a #REF! or #VALUE! error, check that your cumulative probability column matches the length of your values and that all probabilities are valid numbers.
Unlock Excel Magic with Kutools AI
- Smart Execution: Perform cell operations, analyze data, and create charts—all driven by simple commands.
- Custom Formulas: Generate tailored formulas to streamline your workflows.
- VBA Coding: Write and implement VBA code effortlessly.
- Formula Interpretation: Understand complex formulas with ease.
- Text Translation: Break language barriers within your spreadsheets.
VBA: Generate random values with assigned probabilities
For users who require more automation, or want to generate a larger volume of random values (such as thousands of samples), Excel VBA provides a practical solution that operates faster and more flexibly than worksheet formulas. This method is particularly effective when working with large data sets or producing bulk random results for simulations.
1. Go to Developer Tools > Visual Basic, then in the VBA window, click Insert > Module, and paste the following code into the module:
Sub GenerateRandomWithProbability()
Dim rngValues As Range
Dim rngProbs As Range
Dim n As Long
Dim i As Long
Dim cumProbs() As Double
Dim valList() As Variant
Dim randNum As Double
Dim resultRange As Range
Dim idx As Long
' On Error, ignore
On Error Resume Next
xTitleId = "KutoolsforExcel"
' Select values
Set rngValues = Application.InputBox("Select values range", xTitleId, Type:=8)
' Select probabilities
Set rngProbs = Application.InputBox("Select probabilities range", xTitleId, Type:=8)
' Number of random values to generate
n = Application.InputBox("Number of random values to generate", xTitleId, "10", Type:=1)
' Where to output
Set resultRange = Application.InputBox("Select output start cell", xTitleId, Type:=8)
If rngValues.Rows.Count <> rngProbs.Rows.Count Then
MsgBox "Values and probabilities range must be the same size.", vbExclamation
Exit Sub
End If
ReDim cumProbs(1 To rngValues.Count)
ReDim valList(1 To rngValues.Count)
' Calculate cumulative probabilities
cumProbs(1) = rngProbs.Cells(1, 1).Value
valList(1) = rngValues.Cells(1, 1).Value
For i = 2 To rngValues.Count
cumProbs(i) = cumProbs(i - 1) + rngProbs.Cells(i, 1).Value
valList(i) = rngValues.Cells(i, 1).Value
Next i
' Generate random results
For i = 1 To n
randNum = Rnd
For idx = 1 To UBound(cumProbs)
If randNum <= cumProbs(idx) Then
resultRange.Cells(i, 1).Value = valList(idx)
Exit For
End If
Next idx
Next i
End Sub2. In the VBA window, click the 
run button to execute the code. In sequence, you will be prompted to select your values range, probabilities range, the number of random values you wish to generate, and the output starting cell. The macro will quickly fill the target cells with random values based on your assigned probabilities.
- You can repeat this process for larger datasets, and the output column can be customized in any sheet.
- If your probability range does not sum very close to1, the code may skew the distribution; always validate your assumptions.
- This solution is preferable for automated sampling, simulation, and reproducible batch generation.
Tips: Keep your values and probabilities contiguous and clearly aligned. Save your work before running macros, as VBA actions cannot be reverted with Undo.
Relative Articles:
- 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!
All Kutools add-ins. One installer
Kutools for Office suite bundles add-ins for Excel, Word, Outlook & PowerPoint plus Office Tab Pro, which is ideal for teams working across Office apps.
- All-in-one suite — Excel, Word, Outlook & PowerPoint add-ins + Office Tab Pro
- One installer, one license — set up in minutes (MSI-ready)
- Works better together — streamlined productivity across Office apps
- 30-day full-featured trial — no registration, no credit card
- Best value — save vs buying individual add-in