Rand excel formula is a formula which generates random values in cells, this formula is used in sampling purposes when we have a large amount of data sets and we want a sample of population data which is random, this function generates any random value between 0 to 1, it is an inbuilt function in excel.
Rand Excel Formula (Table of Contents)
Rand Formula in Excel
RAND Formula in Excel is used to get random values. The random numbers would be greater than 0 and less than 1 which are evenly distributed. But these values may change when we perform recalculation.
Formula for RAND()
- No need to give any criteria as by default the random values will be between 0 to 1.
How to Use RAND Formula in Excel?
Below are the examples of RAND excel Formula.
Example #1 – Using RAND() to get the Random Values
Let’s assume that we want to generate some random numbers which should be between 0 to 1 then we can use RAND function as below:
We get the Random number as 0.00084.
Similarly, we can list the random numbers by taking the RAND Excel formula in different cells for further calculation as per our requirement.
Example #2 – Using 100 to Convert RAND() Number to be between 10 to 99
Assume that we need to create random numbers but should be in between 10 to 99 then we can multiply the RAND() with 100 and we get the result which would be between 10 to 99 as follows:
RAND Excel Formula will give us a random number that would be between 0 to 1 and when it gets multiply with 100 the number will get converted in between 10 to 99 as the two numbers after the decimal point of random number (which was generated by RAND()) will get shifted to left side of the decimal point.
The number from our example would be as follows:
Similarly, we can generate numbers by using 10, 100, 1000, and so on based on our criteria required for calculations.
Example #3 – Using INT() along with RAND()
As we know that RAND Excel Formula will give random numbers that are from 0 to 1 and we can use multiples of 10, 100, 1000 and so on to get some decimal number which is more than 1. We can observe that the number will be always with decimal points. In order to get integer number from the random numbers that are decimal numbers by using INT() function along with RAND formula in Excel are as follows:
In the above example, we gave the multiple of 100 for a better understanding of the number change. The result would be as follows:
We can observe the number with decimals 0 and in this way, we can convert the number into integers.
Example #4 – Using ROUND() along with RAND()
In order to round the random number as per defined decimals points we can use ROUND() function along with RAND Formula in excel as follows:
Basically ROUND() will take a number to round but we need to give criteria for the number of digits to be displayed after the number gets rounded off. Let’s see how the result would be:
As RAND() will get the random number between 0 to 1 and by using ROUND() with 0 criteria we get the result 1 as this is the rounded number.
Similarly, we can use multiple of 10,100 and so on and also give different criteria of decimal points for ROUND() as below:
And the result would be as follows:
Example #5 – Another Important Feature of the Random Number is RANDBETWEEN()
We also got another important function which is related to random numbers generation is RANDBETWEEN() function. The main difference between RAND() and RANDBETWEEN() is RAND() will give random numbers between 0 and 1 but for RANDBETWEEN() we should give criteria with bottom and random numbers which will generate the random numbers in between the criteria that we would have mentioned.
Let’s see an example below:
We will get that random number which would be between 1000 and 5000 and the result is:
Similarly, we can get any random number that can be in between the given criteria.
Example #6 – Creating Criteria by generating Formula which includes RAND()
If we want to create some random numbers between two numbers using RAND() can be done by generating formula of it.
This is quite similar to RANDBETWEEN() but here we should give a proper formula which satisfies the condition as follows.
Suppose we want to create random values between 50 and 500 then assume a = 50 and b = 500. The formula would be
a + (b-a) * RAND()
Let’s see how it works in excel as below:
The above calculation will go as per BODMAS rule which calculates the bracket values first that gives 500-50 = 450. Then multiply will comes and calculates 450 * RAND() which gives random value that is a multiple of 450. In the last addition will come and add the random value that is multiple of 450 and add back to 50 which would be number between 50 and 500 but not out of these as our formula defined in such a way.
Similarly, we can give any numbers in the place of a and b to get the random numbers between them.
Things to Remember
- We can observe that the new value will be calculated or displayed whenever the RAND() function is dragged down or worksheet gets updated. We can use “Paste special” or “F9”. In order to stop these values from getting updated.
Copy (Ctrl+C) the cell that has the random value with formula and use right click or Ctrl+Alt+V to get the paste special window and then select the values as shown in the above figure.
Enter the formula RAND() in any cell and then press F9 instead of entering and you can see the random value without formula linked to it.
- In order to have a list of random values we can use excel shortcut CTRL+D as follows:
First, we have to type the formula in a cell and drag as shown in the figure including the cell that has the formula and click CTRL+D.
- We can use ROUND(), INT() and many more along with RAND() in order to get better random values which can be used for further operations.
- In order to get the values in terms of 10, 100, 1000 and so on, we can multiply the RAND() with that particular number.
- We can also get the random numbers between any type of criteria by generating certain equation as per the requirement.
This has been a guide to Rand Excel Formula. Here we discuss how to Use Rand Formula in Excel to get the random values with examples and downloadable excel template. You may learn more about excel from the following articles –