RAND and RANDBETWEEN Functions in Google Sheets

Published on

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:

Prashanth KV
Prashanth KV
Your Trusted Google Sheets and Excel Guide Prashanth KV brings a wealth of experience in Google Sheets and Excel, cultivated through years of work with multinational corporations in Mumbai and Dubai. As a recognized Google Product Expert in Docs Editors, Prashanth shares his expertise through insightful blogging since 2012. Explore his blog for practical tips and guidance on maximizing your spreadsheet skills.

Hierarchical Numbering Sequences in Excel

Creating hierarchical numbering sequences in an Excel spreadsheet can significantly improve the way you...

How to Easily Repeat a Sequence of Numbers in Excel

Excel offers multiple ways to accomplish tasks, and the simplicity of each approach depends...

Create a Sequence of Dates at Every Nth Row in Excel (Dynamic Array)

Would you like to create a sequence of dates in every nth row in...

XMATCH Row by Row: Finding Values Across a Range in Google Sheets

Using the BYROW function with XMATCH in Google Sheets allows us to match values...

More like this

XMATCH Row by Row: Finding Values Across a Range in Google Sheets

Using the BYROW function with XMATCH in Google Sheets allows us to match values...

Limit Formula Expansion to a Specific Row in Google Sheets

In this tutorial, I’ll explain how to limit the expansion of an array formula...

3-D Referencing Structured Data Tables in Google Sheets

When you have several tables within a single sheet—not across multiple sheets in a...

LEAVE A REPLY

Please enter your comment!
Please enter your name here

This site uses Akismet to reduce spam. Learn how your comment data is processed.