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.
Get a random number between 0 and 1
A number between 1 and 0
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
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...
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:
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:
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 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.
Formulas are the key to getting things done in Excel. In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Instant access. See details here.