Generate Random Numbers or Text Between Limits in Google Sheets

We can generate either random numbers or text easily in Google Sheets. There are some built-in functions for this purpose. These functions are the RANDBETWEEN, RAND, RANDARRAY functions, etc. By combining them with different other functions we can generate random numbers or text under different conditions. In this article, we’ll see 8 easy ways to generate random numbers between limits in Google Sheets with clear steps and images. We’ll also see 3 easy ways to generate random text between limits in Google Sheets.

A Sample of Practice Spreadsheet

You can download Google Sheets from here and practice very quickly.

8 Easy Ways to Generate Random Numbers Between Limits in Google Sheets

Let’s get introduced to our dataset first. Here we have the lower limit of some numbers in Column B and their upper limit in Column C. We want to generate some random numbers between these limits. So, I’ll show you 8 easy ways to generate random numbers between limits in Google Sheets below with the help of this dataset.

1. Using RANDBETWEEN Function

We can easily generate any random number between 2 limits by using the RANDBETWEEN function. But this function only generates random integers. You can’t get any decimal number by using this function. Let’s see the steps to generate random numbers between limits in Google Sheets by using this method.

Steps:

• Firstly, type the following formula in Cell D5–

• Secondly, hit Enter to get the result.

• Then, apply the Fill Handle tool to use the formula in the rest of the cells of Column D.

• Next, you’ll get the random numbers between those limits in Column D.

• After, you may notice that the previous number in Cell D5 was 6 but now it is 2. This happens because when we use the RANDBETWEEN or RAND functions, the random numbers automatically change after every new operation.
• You can change the frequency of changing the random numbers by the following steps.
• At first, go to File > Settings.

• After that, go to the Calculation window.
• Here I have selected the On change option under the Recalculation menu. You may select the other 2 options; On change and every minute or On change and every hour. These are the frequencies of how the random numbers would change.
• Thereafter, click on the Save Settings button to save your desired setting.

• Finally, you may notice that the random numbers in Column D have changed again.

2. Applying RAND Function

Conversely, the RAND function can generate any decimal numbers between 0 to 1. The limitation of this function is that you will not get any integers or values greater than 1 by applying the RAND function alone.

Steps:

• First, write the following formula in Cell B5–

• Afterward, press Enter to get the output.

• Consequently, use the Fill Handle tool to apply the formula for the rest of the cells of Column B.

• Ultimately, you’ll get the random decimal numbers between 0 to 1 in Column B.

3. Combining ROUND and RAND Functions

In the previous method, the RAND function gives the output but the output is very long. So now we’ll belittle them up to 2 decimal places by combining the ROUND and RAND functions. The RAND function will give random numbers between 0 to 1 and the ROUND function will make them rounded up to any given decimal place.

Steps:

• First of all, insert the following formula in Cell B5–

• Again, click Enter to get the random decimal number.

• Moreover, apply the Fill Handle tool in the rest of the cells of Column B.

• Further, you’ll find some random decimal numbers between 0 to 1 in Column B.

• Now we’ll minimize them up to 2 decimal places.
• That’s why put the following formula in Cell C5–

• Also, tick Enter to get the random decimal number up to 2 decimal places.

• Apart from this, use the Fill Handle tool for the rest of the cells of Column C.

• In the end, there will be some random decimal numbers up to 2 decimal places between 0 to 1 in Column C.

Similar Readings

• How to Link Cells Between Tabs in Google Sheets (2 Examples)
• Calculate Number of Years Between Two Dates in Google Sheets
• How to Move Between Tabs in Google Sheets (3 Easy Ways)
• Google Sheets Count Cells Between Two Numbers with COUNTIF Function
• How to Use IF Condition Between Two Numbers in Google Sheets

4. Merging RAND Function with a Custom Formula

We can also remove the limitation of the RAND function by merging it with a custom formula. We’ll use (20-1)+1 as a custom formula. This formula enables the RAND function to give output between 1 to 20 instead of 0 to 1. Let’s see how to do it.

Steps:

• In the first place, type the following formula in Cell B5–

• Besides, hit the Enter button to get the desired result.

• In addition to that, apply the Fill Handle tool to use the formula for the rest of the cells of Column B.

• Lastly, you’ll get the random decimal number between 1 to 20 in Column B.

5. Uniting TRUNC and RAND Functions

When we combined the RAND function with the custom formula in the previous method, we got the output in decimal numbers. But we want the output as a round figure. So, now we’ll make the output round figure by uniting the TRUNC and RAND functions. The TRUNC function makes any decimal numbers into integers.

Steps:

• In the beginning, write the following formula in Cell B5–

• Subsequently, press the Enter button to get the desired output.

• Then, use the Fill Handle tool in the rest of the cells of Column B.

• Next, there will be some random decimal numbers between 1 to 20 in Column B.

• After that, insert the following formula in Cell C5–

• Thereafter, click the Enter button to get the result.

• Afterward, apply the Fill Handle tool for the rest of the cells of Column C.

• Finally, you’ll see some random numbers as a round figure between 1 to 20 in Column B.

6. Using RANDARRAY Function

Moreover, we can generate random numbers between 0 to 1 across any given range by using the RANDARRAY function. This function gives some random numbers across specified columns and rows with a single click. You’ll find the process below.

Steps:

• Before all, put the following formula in Cell B5–

• Consequently, tick the Enter button to get some random numbers between 0 to 1 across 5 rows and 3 columns starting from Cell B5.

Read More: Insert Rows Between Other Rows in Google Sheets (4 Easy Ways)

Similar Readings

• How to Calculate Time Between Dates in Google Sheets (6 Ways)
• Use REGEXEXTRACT Function Between Two Characters in Google Sheets
• How to Remove Spaces Between Words in Google Sheets
• Find Difference Between Two Columns in Calculated Field of Google Sheets Pivot Table
• How to Find Correlation Between Two Columns in Google Sheets

7. Combining ARRAYFORMULA, ROUND and RANDARRAY Functions

At this moment we want to generate some random numbers between 1 to 10 across a given range in integer form with just a single click. So we have to combine the ARRAYFORMULA, ROUND, and RANDARRAY functions for this purpose.

Steps:

• Earlier on, type the following formula in Cell B5–

• Again, hit Enter to get some random integers between 1 to 10 across 5 rows and 3 columns starting from Cell B5.

Read More: How to Find Unique Values Between 2 Columns in Google Sheets

8. Using Apps Script

We can make a random number generator button in Google Sheets by using the Apps Script Extensions. We have to insert a code for this purpose. This button will generate random numbers quickly between 0 to 1 with just a single click across any selected cells. Below are the steps for making the random number generator button.

Steps:

• Before, go to Extensions > Apps Script.

• Moreover, rename the file as “Random Numbers” and insert the following code there:

function fillRandom() {
var data = SpreadsheetApp.getActiveSheet();
var values = data.getActiveRange();
for (var col = 1; col <= values.getWidth(); col++) {
for (var row = 1; row <= values.getHeight(); row++) {
values.getCell(row, col).setValue(Math.random());
}
}
};
function onOpen() {
var dataset = SpreadsheetApp.getActiveSpreadsheet();
var numbers = [{
name : "Fill",
functionName : "fillRandom"
}];
dataset.addMenu("Random", numbers);
}

• Apart from this, save the code by clicking on the Save button as shown below.
• Then, select On Open from the menu and click on the Run button.
• Here, the function name is “fill random” and the operation it will do is given the name “Fill”. You’ll find a new menu name “Fill” in your Google Sheets.

• If you are using the Apps Script for the first time, you have to give authorization.
• So, click on the Review Permissions button in the Authorization Required window.

• Next, click on the Go to Random Numbers (unsafe) button.

• After that, the Apps Script will want your permission.
• So, give the permission by selecting the Allow button.

• Then, you’ll find a Random number generator button created in your Google Sheets.

• Select cells from Cells B5 to D9 and then go to Random > Fill.

• Ultimately, the random numbers will be automatically generated across the selected cells.

3 Easy Ways to Generate Random Text Between Limits in Google Sheets

Now, we’ll see another dataset below. Here we have some text in Column B. We want to generate a random text between this list. So, I’ll show you 3 easy ways to generate random text between limits in Google Sheets by using this dataset.

1. Merging INDEX, RANDBETWEEN and COUNTA Functions

We can merge 3 functions together to generate a random text among several texts. These functions are the INDEX, RANDBETWEEN, and COUNTA functions. Let’s see the steps below.

Steps:

• Initially, write the following formula in Cell C5–

• Then, press Enter to get a random text between these texts. Each time you enter the formula, you will get new output.

Read More: Difference Between COUNT and COUNTA in Google Sheets

2. Joining CHOOSE and RANDBETWEEN Functions

Apart from the previous method, we can join the CHOOSE and RANDBETWEEN functions to make a choice among several choices and gives the output as text. But the problem with this method is that we have to give the choices one by one. We can not insert them directly into our formula.

Steps:

• Before all, insert the following formula in Cell C5–

• Next, click Enter to get the random text.

3. Uniting SORT and RANDARRAY Functions

Last but not the least, we’ll use the SORT and RANDARRAY functions to obtain a random list of texts from a given list of texts. The RANDARRAY function will give the random output and the SORT function will sort them randomly.

Steps:

• Foremost, put the following formula in Cell C5–

• After that, hit the Enter button to get the random texts sorted.

Conclusion

That’s all for now. Thank you for reading this article. In this article, I have discussed 8 easy ways to generate random numbers between limits in Google Sheets. I have also discussed 3 easy ways to generate random text between limits in Google Sheets. Please comment in the comment section if you have any queries about this article. You will also find different articles related to google sheets on our officewheel.com. Visit the site and explore more.

Related Articles

• How to Calculate Hours Between Two Times in Google Sheets
• Calculate Percentage Difference Between Two Numbers in Google Sheets
• How to Find Missing Values Between Two Columns in Google Sheets
• Conditional Formatting Between Two Values in Google Sheets
• How to Insert Lines Between Cells in Google Sheets
• Find Number of Months Between Two Dates in Google Sheets
• How to SUMIF Between Two Dates in Google Sheets (3 Ways)