Excel RAND Function
Summary
The Excel RAND function returns a random number between 0 and 1. For example, =RAND() will generate a number like 0.422245717. RAND recalculates when a worksheet is opened or changed.
Purpose
Get a random number between 0 and 1
Return value
A number between 1 and 0
Syntax
=RAND ()
Arguments
Usage notes
- RAND calculates a new value each time the worksheet is calculated. To stop random numbers from being updated, copy the cells that contain RAND to the clipboard, then use Paste Special > Values to convert to text.
- To get a random number that doesn't change when the worksheet is calculated, enter =RAND() in the formulas bar and then press F9 to convert the formula into its result.
- To generate a set of random numbers in multiple cells, select the cells, enter RAND() and press control + enter.
- To generate a random number between a and b, use this formula: RAND() * (b - a) + a
RAND formula examples
To generate random times in at specific intervals you can use the RAND function with the FLOOR function. In the example shown, the formula in B6 is:
=FLOOR(RAND(),"0:15")
which generates a random time at a...
To generated a random number, weighted with a given probability, you can use a helper table together with a formula based on the RAND and MATCH functions.
In the example shown, the formula in F5 is:
=MATCH(RAND(),D$5...
To randomly assign people to groups or teams of a specific size, you can use a helper column with a value generated by the RAND function, together with a formula based on the RANK and ROUNDUP functions. In the example...
Related videos
In this video, we'll look at a few ways to generate random values with the RANDBETWEEN function. You can use this approach to generate random numbers, random prices, random dates, and even random text values. This is a great way to create dummy data to test formulas and worksheets.
In this video, we look at how to simulate a drawing and pick winners in a contest with Excel, using the RAND and RANK functions together.
In this video, we look at a step by step approach to randomly assign people to groups with teams using formulas. Along the way, we use three functions (RAND, RANK, and CEILING), helper columns, and nesting. We verify results with the COUNTIF function.
Related functions
Good links
