How to Generate Random Numbers in Excel
What do lottery balls, dice, and a roulette wheel have in common? They are based on randomness, which cuts across numerous fields and exists in different forms.
You might want to generate random numbers to test a financial model, create sample data, or simulate sports fixtures.
This article takes a look at how to generate random numbers in Excel.
CONTENTS 1. Download Examples 2. Generate Random Numbers with RAND a) Syntax b) Generate Random Decimals Between a Range c) Generate Random Integers 3. Generate Random Numbers Between a Range with RANDBETWEEN a) Syntax b) Generate Random Decimals 4. Generate Random Numbers with RANDARRAY a) Syntax 5. Generate Non-Repeating Random Numbers 6. Final Words 7. Resources
Download Examples
Generate Random Numbers with RAND
RAND is one of the two original functions for generating random numbers.
It generates a number with up to 17 decimal places that is equal to or greater than 0 and less than 1.
There are no arguments, so just place an empty pair of parentheses next to the function.
Syntax
=RAND()Only the first 9 decimal places are displayed in these examples, even though the actual amount is greater. Repeatedly click the Increase Decimal button in Home (tab) > Number (group) to see the others.
Generate Random Decimals Between a Range
To return a random decimal greater than or equal to 0 and less than max, multiply RAND() by max.
=RAND()*max
Formula to return a random decimal greater than or equal to 0 and less than 5:
=RAND()*5To start from 1, multiply RAND() by the result of max minus 1, and then add 1.
=RAND()*(max-1)+1
Formula to return a random decimal greater than or equal to 1 and less than 5:
=RAND()*4+1If you want control over the minimum value, use this syntax:
=RAND()*(max-min)+min
Formula to return a random decimal greater than or equal to 10 and less than 20:
=RAND()*(20-10)+10Generate Random Integers
With the help of ROUND, a decimal can be rounded to the nearest whole number by setting num_digits to 0.
The number multiplied by RAND() needs to be one less than the intended upper limit.
Formula to return a random integer greater or equal to 0 and less than 5:
=ROUND(RAND()*4,0)If you want to include the upper number as a possibility, you can either go one higher in the previous formula, or you can wrap INT around the calculation to chop off the decimal part.
The given number must be one greater than the upper limit.
Formula to return a random integer between (and including) 0 and 5:
=INT(RAND()*6)Generate Random Numbers Between a Range with RANDBETWEEN
RANDBETWEEN is the other original function. It returns a random integer between (and including) two specified values.
Syntax
=RANDBETWEEN(bottom,top)bottom— the minimum value to returntop— the maximum value to return
Formula to return a random integer greater than or equal to 1 and greater or equal to 5:
=RANDBETWEEN(1,5)Generate Random Decimals
RANDBETWEEN is designed to work with integers only; however, it is possible to coerce it to return decimals.
To return a random number rounded to one decimal place, multiply the bottom and top values by 10, and then divide the result by the same number.
=RANDBETWEEN(bottom*10,top*10)/10
Formula to return a random decimal (to 1 d.p.) between (and including) 1 and 5:
=RANDBETWEEN(1*10,5*10)/10If you wanted to round to two decimal places, you would use 100 instead.
Formula to return a random decimal (to 2 d.p.) between (and including) 1 and 5:
=RANDBETWEEN(1*100,5*100)/100Generate Random Numbers with RANDARRAY
So far, you’ve seen how to generate one random number for one cell. RANDARRAY enables you to generate a set of random numbers using a single formula. It is only available in Excel for Microsoft 365 and Excel 2021, however.
Syntax
=RANDARRAY([rows],[columns],[min],[max],[integer])[rows]— the number of rows to return[columns]— the number of columns to return[min]— the minimum value to return[max]— the maximum value to return[integer]— specify TRUE to return whole numbers or FALSE (default) for decimal ones
RANDARRAY can be used with empty parentheses like RAND; however, the former includes 1 as the highest possible number.
By setting a [rows], [min] and [max] number, you can return multiple random decimals with a sole formula.
Formula to return 5 random decimals between (and including) 1 and 5:
=RANDARRAY(5,,1,5)RANDARRAY also makes it easier to return between any two numbers.
Formula to return 5 random decimals between (and including) 10 and 20:
=RANDARRAY(5,,10,20)Include TRUE in [integer] to ensure only integer numbers are returned.
Formula to return 5 integer numbers greater than or equal to 1 and less than or equal to 5:
=RANDARRAY(5,,1,5,TRUE)You can return a multi-dimensional array by setting a [columns] value.
Formula to return a 5×3 (rows x columns) array of random integer numbers between (and including) 1 and 100:
=RANDARRAY(5,3,1,100,TRUE)Generate Non-Repeating Random Numbers
So far, all the methods you’ve seen can return repeating numbers. This is very rare for decimals as they are rounded up to 17 places, but it’s far more likely for integers.
Thankfully, there is a workaround that uses RANDARRAY in conjunction with SEQUENCE and SORTBY.
=SORTBY(
SEQUENCE(max),
RANDARRAY(max)
)Max is the upper value included in the rows and [rows] arguments of SEQUENCE and RANDARRAY, respectively.
SEQUENCE guarantees an array of unique numbers sorted by the decimals RANDARRAY produces. This makes it impossible to get duplicates, as the SEQUENCE figures will always take precedent if RANDARRAY returns repeats.
Formula to return 10 integers between (and including) 1 and 10:
=SORTBY(
SEQUENCE(10),
RANDARRAY(10)
)If you require a larger range of possible values than the quantity itself, place the statement you just saw in INDEX’s array argument and use SEQUENCE in row_num to return a certain amount of rows.
=INDEX(
SORTBY(
SEQUENCE(20),
RANDARRAY(20)
),
SEQUENCE(rows)
)
Formula to return 10 integers between (and including) 1 and 20:
=INDEX(
SORTBY(
SEQUENCE(20),
RANDARRAY(20)
),
SEQUENCE(10)
)If you want the minimum number to be something other than 1, you’ll need to include a minimum value in the [start] argument of the first SEQUENCE and ensure the formula conforms to this syntax:
=INDEX(
SORTBY(
SEQUENCE(max-min,,min),
RANDARRAY(max-min)
),
SEQUENCE(rows)
)
Formula to return 10 integers greater than or equal to 10 and less than 50:
=INDEX(
SORTBY(
SEQUENCE(40,,10),
RANDARRAY(40)
),
SEQUENCE(10)
)To create a grid of random unique numbers, specify a value in the [columns] argument of the second SEQUENCE.
Formula to return a 5×3 array of unique integers greater than or equal to 10 and less than 50:
=INDEX(
SORTBY(
SEQUENCE(40,,10),
RANDARRAY(40)
),
SEQUENCE(5,3)
)Final Words
You’ve just learnt a multitude of ways to generate random numbers in Excel, whether that’s integers, decimals, or non-repeating numbers.
It’s important to remember that despite the range of options, the numbers are never truly random.
Excel 2003 and Excel 2007 used the Wichman & Hill RNG algorithm to generate random numbers between zero and one; however, it was flawed as negative numbers were also returned.
Its successor, Mersenne Twister (Excel 2010 and beyond), does pass all the standard randomness tests, but the numbers are still pseudo-random. That means, in theory, it is possible to unravel the algorithm to predict the next number.
For true randomness, there would have to be an event(s) occurring outside the computer environment. Websites like random.org use atmospheric noise for their generators, but Excel’s functions are sufficient for most purposes.
Resources
I first posted about generating random numbers on LinkedIn.
https://www.linkedin.com/feed/update/urn:li:activity:6888158407568752640/
You should learn how to convert formulas into lambdas — it’s a real time-saver! I founded RANDUNIQUE, which returns a set of unique random numbers based on given parameter values.
https://www.linkedin.com/feed/update/urn:li:activity:6888158407568752640/
I also built an Excel cup draw simulator, which mimics the ones you see on the telly! It’s a good practical example of how you might apply the concepts discussed.
https://www.linkedin.com/feed/update/urn:li:activity:6888158407568752640/
