How to Generate Random Variables from a Truncated Normal Distribution in Excel
Question details
The user needs to generate random numbers that follow a normal distribution curve but are strictly restricted within specific lower and upper bounds.

- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Creating datasets for statistical analysis, Monte Carlo simulations, or testing models where variables (like test scores or physical measurements) cannot exceed realistic boundaries.
- Observed behavior
- Excel provides standard statistical functions but lacks a single built-in function for truncated normal distributions, requiring users to build custom formulas or use boundary rejection methods.
Before generating your random variables, clearly define the desired Mean, Standard Deviation, and the absolute Lower and Upper limits for your data.
Use the Inverse Transform Sampling Formula
This method is the most efficient as it mathematically calculates bounded random probabilities and converts them directly to truncated normal distribution variables without needing to manually delete outliers.
By utilizing the cumulative distribution function (CDF), you can limit the random probabilities generated by the RAND() function to fall strictly between the probabilities of your lower and upper bounds.
Enter your Mean, Standard Deviation (SD), Lower Bound, and Upper Bound into separate cells (e.g., A1 through A4) for easy referencing.
Use the NORM.DIST function to find the probability at your boundaries. For example, NORM.DIST(Lower, Mean, SD, TRUE) gives the lower bound probability.
In your target cell, combine the functions to generate the truncated random variable. The formula structure is: =NORM.INV(NORM.DIST(Lower, Mean, SD, TRUE) + RAND() * (NORM.DIST(Upper, Mean, SD, TRUE) - NORM.DIST(Lower, Mean, SD, TRUE)), Mean, SD).
Press Enter to generate your first random variable, then drag the fill handle down to populate the rest of your simulation dataset.

Use the Rejection Method (Generate and Filter)
Generate standard normal distribution variables and simply filter out or reject any values that fall outside your required limits.
Easily Generate Statistical Distributions in WPS Spreadsheet
WPS Spreadsheet fully supports advanced statistical functions like NORM.INV and NORM.DIST, allowing you to seamlessly generate truncated normal distributions and run Monte Carlo simulations just as you would in Microsoft Excel.
- 1. Launch WPS Spreadsheet: Open your dataset or create a new workbook in WPS Spreadsheet.
- 2. Input the parameters: Enter your Mean, Standard Deviation, Lower Bound, and Upper Bound into designated cells.
- 3. Enter the formula: Type the combined NORM.INV and NORM.DIST formula into your starting cell to apply the boundary constraints.
- 4. Fill the data series: Drag the fill handle downward to instantly generate your complete set of bounded random variables.

Frequently Asked Questions
Why does Excel not have a TRUNC.NORM function?
Excel's built-in statistical functions cover standard and generic distributions. Truncated distributions are considered specialized cases, so users must combine standard functions like NORM.INV, NORM.DIST, and RAND to mathematically enforce boundaries.
Can I use VBA to generate truncated normal random variables?
Yes, you can write a custom VBA User Defined Function (UDF) that uses a Do-While loop to generate standard normal random variables using WorksheetFunction.Norm_Inv, automatically rejecting and regenerating values until it finds one within your specified bounds.
Does the rejection method skew the normal distribution curve?
No, as long as the underlying random numbers generated are properly normally distributed, cutting off the tails (rejecting values outside the bounds) creates a mathematically accurate truncated normal distribution for the remaining range.
Will these random values change every time I edit the sheet?
Yes, because the RAND() function is volatile, it recalculates automatically whenever the worksheet updates. To keep your generated simulation values permanent, select the data, copy it, and use 'Paste as Values' to overwrite the formulas with static numbers.




