Excel’s Random Functions (RAND and RANDBETWEEN)
Do you want to create random numbers? Excel’s random functions (RAND and RANDBETWEEN) can help you do this easily. Let’s explore the formula, usage, and examples for this interesting group of random functions in Excel with Buffcom.net!
RAND Function in Excel
RAND stands for Random in English. This function will return a random decimal number within the range you specify.
RAND Formula in Excel
The RAND function syntax to return a decimal number greater than or equal to 0 and less than 1 is:
If you want to create a random decimal number greater than or equal to 0 and less than n, use the following syntax: =RAND()*n
General rule: To create a random real number between a and b, use the formula =RAND()*(b-a)+a
- This function has no arguments.
- When the worksheet is updated (such as when a formula/data is entered into another cell, when the Excel file is opened or closed…), the result of the RAND function will change.
- If you use the RAND function to generate a random number but do not want the result to change every time the cell is recalculated, after entering the formula, press F9 -> Enter. The result in the cell will return as a value and will not change.
Examples of RAND Function
Returns a random decimal number greater than or equal to 0 and less than 1:
Returns a random decimal number greater than or equal to 3 and less than 200:
Generating Random Integer by Combining INT and RAND Functions
So how can we generate a random integer using the RAND function? We will combine the RAND function with the integer extraction function (INT) in Excel.
For example, to get a random integer less than 100 and greater than or equal to 0 (0<=x<100, where x is an integer), enter the syntax: =INT(RAND()*100)
Generating a Random Character using RAND, INT, and CHAR Functions
Instead of generating a random decimal number, what if you want the result to be a character in the alphabet?
To get a random character in the English alphabet consisting of 26 letters, first, we need to get a random integer between 1 and 26. Combining the RAND and INT functions as mentioned above, we have the formula: =INT(RAND() * 26 + 1)
According to the ANSI table, uppercase letters (A to Z) range from ANSI code 65 to ANSI code 90. Use the CHAR function with the formula: =CHAR(INT(RAND() * 26 + 1) + 64)
Similarly, lowercase letters from a to z range from ANSI code 97 to ANSI code 122. Use the CHAR function with the formula:
=CHAR(INT(RAND() * 26 + 1) + 96)
RANDBETWEEN Function in Excel
The RANDBETWEEN function returns a random integer within a specified range. The returned integer can be negative or positive. It is much simpler than combining the INT and RAND functions, isn’t it?
RANDBETWEEN Function Formula
Syntax of RANDBETWEEN function:
=RANDBETWEEN(upper limit, lower limit)
Upper limit: (required) the largest integer that the function will return
Lower limit: (required) the smallest integer that the function will return
- Like the RAND function, the result of the RANDBETWEEN function also changes when the worksheet is updated (for example, by entering a formula or data into another cell, or opening and closing the Excel file). If you do not want the result to change, press F9 -> Enter after entering the formula to return the function in an unchanged value format.
- The RANDBETWEEN function can be used to randomly select dates
Example for RANDBETWEEN Function
Get a random integer between -10 and 10
Through this article, we hope you have learned the syntax and how to use the random function group in Excel, including the RAND and RANDBETWEEN functions. Follow Buffcom.net to update useful office computer knowledge.
In addition, if your job is closely related to office computer knowledge but the software does not meet your needs, please contact Buffcom.net to find a suitable product for your needs!