

Sometimes, you want to create random values in Excel. For example, when you want to simulate some kind of statistical distribution, make up random key codes or want to pre-fill some database. In this article, we’ll take a look at the two Excel formulas RAND and RANDBETWEEN with a lot of examples.
Contents
The basic RAND formula returns a random value between 0 and 1
There is a simple formula to generate random numbers: RAND. If you type =RAND() into a cell, you’ll get a number between 0 and 1.
Two comments:
That’s the easy case – let’s take it a step further.
Create random numbers with decimals and a minimum and maximum value.
What do you do, if you want to have random number between 20 and 50 for example?
Use the RAND formula and do some maths: The interval is 30 (=50-20). We have to multiply =RAND() by 30. That means, the random number will be between 0 and 30. Now we only have to add 20. The result is then always between 20 and 50. If you want to create whole numbers, please add the ROUND formula.
In more mathematical terms: If you want to get a random number between a and b, you have to type
=RAND()*(b-a)+a
Hold on a second. Was this information helpful so far? ...or on other networks! Twitter: Follow @professorexcel
Facebook:
RANDBETWEEN returns integer numbers (whole numbers) with a minimum and maximum value.
If you want to get whole number you got two options: Either you use the method from our example before and wrap it into the ROUND formula with 0 decimals. Or you use the more elegant soluation: Use the RANDBETWEEN formula.
RANDBETWEEN works similar to RAND but has two arguments: the bottom (in our case 20) and the top value (in our example 50). The complete formula looks like this: =RANDBETWEEN(20,50) .
As said before: Please note that RANDBETWEEN only returns integer values. So if you some more decimals you should go with the first option.
Example: Get even or odd random number between -20 and +20.
Let’s take a look at a special example. This example should provide an idea of how to solve more complicated cases. The example: You want to get an even number between -20 and +20.
Let’s set up the formula step by step using the RAND formula:
There is also a slightly shorter way using RANDBETWEEN:
=RANDBETWEEN(-20/2,20/2)*2
One more note: If you want to get odd number, just add 1 in the end (and change the minimum and maximum values accordingly). So if you want to get an odd value between -20 and +20:
=RANDBETWEEN((B21-1)/2,(B22-1)/2)*2+1
Create values with a normal distribution in Excel.
The RAND and RANDBETWEEN produce even distributed values. That means, each value between your minimum and maximum will come up with the same probability. Sometimes you don’t want that. Instead you want to get values with a distribution, let’s say the most popular normal distribution.
In such case you can combine the RAND formula with the NORM.INV formula. That way, you can create random values with a underlying normal distribution.
You need to specify:
If you put these input values together (let’s say with the mean of 10 and the standard deviation of 5), the formula looks like this:
=NORM.INV(RAND(),10,5)
Get random letters (small or capital) in Excel
You can also create random letters. Therefore you have to use a so-called ASCII code: Every character has a number (not only in Excel). Here is a list of ASCII codes. You only have to wrap CHAR formula around the RANDBETWEEN formula (with the correct minimum and maximum values). That way, Excel translates your random number into the corresponding character.
The formula for capital letters (they occupy numbers 65 to 90 among the ASCII codes):
=CHAR(RANDBETWEEN(65,90))
The formula for small letters:
=CHAR(RANDBETWEEN(97,122))
Some more comments on both formulas (RAND and RANDBETWEEN):
Has this article helped you? Why don’t you subscribe to our free Excel newsletter?
'Professor Excel Tools': Add more than 60 amazing features to Excel!
Professor's Error Helper for Excel: Solve any error in Excel.
Necessary cookies are absolutely essential for the website to function properly. This category only includes cookies that ensures basic functionalities and security features of the website. These cookies do not store any personal information.
Any cookies that may not be particularly necessary for the website to function and is used specifically to collect user personal data via analytics, ads, other embedded contents are termed as non-necessary cookies. It is mandatory to procure user consent prior to running these cookies on your website.
Subscribe to our popular Excel newsletter! You get all this:
Your welcome gift: Our big 45 pages keyboard shortcuts package.
Subscribe now! In case the sign-up form above doesn't work, please use this page. Sorry for the inconvenience.
Please use this download link to download the Currency Converter Excel add-in!
Please use this download link to download the comment extraction example: http://bit.ly/Extract_Comment_Example