Generate Random Whole Numbers Within a Range in Excel
Question details
Generate random whole numbers from 21 through 25 using a mean and standard deviation stored in other cells.

- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- The user wants multiple random integers based on a normal distribution and limited to a specific range.
- Observed behavior
- NORMINV can return decimal values and numbers outside the desired range.
Enter the mean in cell A1 and a positive standard deviation in cell B1, or identify the cells that already contain them. Adjust the cell references in the formula if your values are stored elsewhere.
Round and Limit Normally Distributed Values
Use this formula when the random values should be based on a mean and standard deviation while staying between 21 and 25.
The formula generates a normally distributed value, rounds it to a whole number, and clamps it to the specified range. Clamping can increase the frequency of 21 and 25, so the final values will not follow an exact normal distribution or necessarily retain the original mean and standard deviation.
Select an empty cell and enter =MAX(21,MIN(25,ROUND(NORM.INV(RAND(),$A$1,$B$1),0))).
Drag the cell's fill handle down or across to copy the formula. The absolute references keep the mean and standard deviation fixed in A1 and B1.
To prevent the numbers from changing on recalculation, copy the formula cells and use Home > Paste > Values.

Generate Uniform Random Integers With RANDBETWEEN
Use this simpler option if you do not need the values to reflect a mean or standard deviation.
Generate Bounded Random Integers in WPS Spreadsheets
Use WPS Spreadsheets to generate random whole numbers based on a mean and standard deviation, then limit them to your chosen range.
- 1. Open a spreadsheet: Launch WPS Office, open Spreadsheets, and create a workbook or open an Excel file.
- 2. Enter the parameters: Enter the mean in A1 and a positive standard deviation in B1.
- 3. Add the formula: Select an empty cell and enter =MAX(21,MIN(25,ROUND(NORMINV(RAND(),$A$1,$B$1),0))).
- 4. Fill the range: Drag the formula cell's fill handle to create additional random integers.

Frequently Asked Questions
Why do 21 and 25 appear more often than expected?
MAX and MIN clamp values below 21 to 21 and values above 25 to 25. This can add probability to the endpoints and change the final distribution.
Why do my random numbers change when I edit the workbook?
RAND and RANDBETWEEN recalculate when Excel recalculates. To keep the current results, copy the cells and choose Home > Paste > Values.
Can I use different cells for the mean and standard deviation?
Yes. Replace $A$1 and $B$1 in the formula with the cells containing your mean and positive standard deviation. Keep the dollar signs if you want those references fixed when copying the formula.
What is the difference between NORM.INV and RANDBETWEEN?
NORM.INV uses a probability, mean, and standard deviation to return a value from a normal distribution. RANDBETWEEN returns an equally likely whole number within the specified bounds and does not use those parameters.




