How To Create A Random Number Generator In Google Sheets

The RAND function in Google Sheets generates a random number between 0 and 1 and can be used as a random number generator in Google Sheets.

Random Number Generator In Google Sheets

The RAND function outputs a decimal number between 0 and 1, for example, 0.2760773217.

RAND Function Notes

=RAND()

It takes no arguments.

The RAND function is a volatile function, which means it recalculates every time a change is made in your Sheet or every time you open your Google Sheet.

RAND volatile function in Google Sheets

If you have a lot of RAND functions in your Sheet (or a lot of downstream formulas that reference a RAND function), this can lead to slow Google Sheets because it will keep recalculating all those formulas with every change.

The RAND function can also be forced to recalculate every hour or every minute, which can be used as a trigger for other actions, as shown in this dynamic table of contents example.

To force recalculations, go to the menu: File > Settings > Calculation

Select the hour or minute options under the Recalculation dropdown menu:

Google Sheet Calculation Settings

How To Generate A Random Number In Google Sheets Between Two Numbers

To generate a random number between two values, use this formula, where X and Y are the lower and upper boundaries:

=RAND()*(Y-X)+X

For example, to create a random number generator in Google Sheets between 1 and 50, use this formula:

=RAND() * (50-1) + 1

Note: To generate a random integer (i.e. a whole number) between two values, use the RANDBETWEEN function.

=RANDBETWEEN(1, 50)

will output an integer between 1 and 50, e.g. 26.

Random Number Generator Referencing Other Cells

The lower and upper boundaries can be referenced in other cells, rather than written directly in the formula.

For example, with a lower bound in A1 and an upper bound in cell A2, this formula generates a random number between them:

=RAND()*(A2-A1)+A1

which looks like this in your Sheet:

RAND function With Cell References

How To Create Random Arrays

Use the RANDARRAY function.

=RANDARRAY(10,3)

RANDARRAY Function Example

How To Create Random Number Between Two Values

Use the RANDBETWEEN function.

=RANDBETWEEN(1,10)

RANDBETWEEN Function In Google Sheets

RAND Function Template

Click here to open a view-only copy >>

Feel free to make a copy: File > Make a copy

If you can’t access the template, it might be because of your organization’s Google Workspace settings. If you right-click the link and open it in an Incognito window you’ll be able to see it.

It’s part of the Math family of functions in Google Sheets. You can read about it in the Google Documentation.

Leave a Reply

Your email address will not be published.