Function Problems

How to Generate Random Variables from a Truncated Normal Distribution in Excel

Tauseeq MagsiTauseeq Magsi Oct 21, 2026 869 views

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.

How to Generate Random Variables from a Truncated Normal Distribution in Excel
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 you start

Before generating your random variables, clearly define the desired Mean, Standard Deviation, and the absolute Lower and Upper limits for your data.

Solution 1Recommended

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.

1
Define your statistical parameters

Enter your Mean, Standard Deviation (SD), Lower Bound, and Upper Bound into separate cells (e.g., A1 through A4) for easy referencing.

2
Calculate the cumulative probabilities

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.

3
Apply the combined formula

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

4
Populate your dataset

Press Enter to generate your first random variable, then drag the fill handle down to populate the rest of your simulation dataset.

Use the Inverse Transform Sampling Formula
Formula Accuracy: Because this formula maps the RAND() function exactly between the cumulative probabilities of your boundaries, every number generated is guaranteed to be within your specified 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. 1. Launch WPS Spreadsheet: Open your dataset or create a new workbook in WPS Spreadsheet.
  2. 2. Input the parameters: Enter your Mean, Standard Deviation, Lower Bound, and Upper Bound into designated cells.
  3. 3. Enter the formula: Type the combined NORM.INV and NORM.DIST formula into your starting cell to apply the boundary constraints.
  4. 4. Fill the data series: Drag the fill handle downward to instantly generate your complete set of bounded random variables.
Fully compatible with Microsoft Excel statistical formulas and array functions.Lightweight software architecture that handles massive simulation datasets smoothly.Free to use with an intuitive, familiar interface for quick data analysis.
microsoft office alternative - wps office

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.