The RAND and RANDBETWEEN functions return a random number in Google Sheets. To return an array of random numbers, you can use the RANDARRAY function.

This tutorial discusses RAND and RANDBETWEEN and how they differ.

Before that, one important point: These functions will recalculate and return a new random number when you enter any data in the sheet, make other changes, reopen it, or according to the recalculation settings explained in this tutorial: Frequency of Recalculation for Volatile Functions in Google Sheets.

RAND Function in Google Sheets

Syntax:

RAND()

The RAND function doesn’t take any arguments. You simply enter it in any cell to get a random number.

Example:

=RAND()

The RAND function in Google Sheets returns a random number between 0 (inclusive) and 1 (exclusive), meaning it generates a random number between 0 and 0.9999999.

You may want a real-life use case for the RAND function in Google Sheets. Here is one:

Assume you want to pick one item randomly from the available two items. You can use the following formula:

=IF(RAND()>0.5, "Apple", "Orange")

You can replace “Apple” with “Heads” and “Orange” with “Tails” to use it for coin flipping. However, for this purpose, you can also use the RANDBETWEEN function or the currently undocumented COINFLIP function.

RANDBETWEEN Function in Google Sheets

Syntax:

RANDBETWEEN(low, high)

Arguments:

Unlike the RAND function, the RANDBETWEEN function takes two arguments.

• low – The low end (the smallest integer) of the random range.
• high – The high end (the largest integer) of the random range.

This formula returns a random integer between two values, both inclusive.

Example:

=RANDBETWEEN(6, 15)

This formula will return a random number between 6 and 15, both inclusive.

Here is one more example:

=TO_DATE(RANDBETWEEN("2024-06-01", "2024-06-30"))

This formula returns a random date between two dates. You can specify dates as the low and high ends of the function.

We use the TO_DATE function with RANDBETWEEN to convert the numeric value returned by the latter to a date.

Additional Tips

In RANDBETWEEN, if you specify 0 and 100 as the lowest and highest values, the random number can be any number from 0 to 100, both inclusive.

How do we make 0 inclusive, but 100 exclusive?

For this, we can use the following RAND and TRUNC combo, which returns a random number between 0 and 99:

=TRUNC(RAND()*100)

• RAND() generates a random decimal number between 0 (inclusive) and 1 (exclusive).
• Multiplying by 100 scales this to a range of 0 to just under 100.
• TRUNC(...) removes the decimal part, leaving an integer between 0 and 99.

If you want a random number between 1 and 100, 1 inclusive, 100 exclusive, use this similar formula.

=TRUNC(RAND()*(100-1)+1)

• RAND() generates a random decimal number between 0 (inclusive) and 1 (exclusive).
• *(100-1) scales this random number to a range between 0 and 99.
• +1 shifts the range to be between 1 and 100.
• TRUNC(...) removes the decimal part, giving you an integer between 1 and 99.

Resources

Here are a few related resources in Google Sheets:

• Pick a Random Name from a Long List in Google Sheets
• Google Sheets: Macro-Based Random Name Picker
• How to Randomly Select N Numbers from a Column in Google Sheets
• How to Randomly Extract a Certain Percentage of the Rows in Google Sheets
• How to Generate Odd or Even Random Numbers in Google Sheets
• Pick Random Values Based on Conditions in Google Sheets