Function Problems

Generate Random Whole Numbers Within a Range in Excel

Phi Hung VoPhi Hung Vo Nov 14, 2026 868 views

Question details

Generate random whole numbers from 21 through 25 using a mean and standard deviation stored in other cells.

Generate Random Whole Numbers Within a Range in Excel
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.
Before you start

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.

Solution 1Recommended

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.

1
Enter the formula

Select an empty cell and enter =MAX(21,MIN(25,ROUND(NORM.INV(RAND(),$A$1,$B$1),0))).

2
Generate more values

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.

3
Keep the current results

To prevent the numbers from changing on recalculation, copy the formula cells and use Home > Paste > Values.

Round and Limit Normally Distributed Values
Recalculation changes results: RAND generates new values whenever Excel recalculates. In older Excel versions, use NORMINV in place of NORM.INV.
WPS Spreadsheets

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. 1. Open a spreadsheet: Launch WPS Office, open Spreadsheets, and create a workbook or open an Excel file.
  2. 2. Enter the parameters: Enter the mean in A1 and a positive standard deviation in B1.
  3. 3. Add the formula: Select an empty cell and enter =MAX(21,MIN(25,ROUND(NORMINV(RAND(),$A$1,$B$1),0))).
  4. 4. Fill the range: Drag the formula cell's fill handle to create additional random integers.
Generate bounded integers with spreadsheet formulasOpen and save Microsoft Excel XLSX filesCopy formulas across cells to create multiple valuesUse a familiar spreadsheet interface
microsoft office alternative - wps office

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.