Excel 365’s dynamic array features include a new way to get a series of random numbers with an option not available in the existing Rand() or RandBetween() functions.
RandArray() fills cells with random numbers for as many rows and columns as you want. The most basic version of the formula is:
=RandArray(3,2)
Fills three rows and two columns with random numbers to 9 decimal places between 0 and 1.
RandArray() with no parameters returns a single cell with a random number between 0 and 1. In other words =RandArray() is the same as =Rand().
OK, yes Pseudo-random numbers. All Excel random numbers are made by a formula so, strictly speaking, they aren’t truly random. Rand() RandBetween() and RandArray() give sufficiently randomized values for all but the most exacting purposes.
Minimum and Maximum
The next two parameters in RandArray() set lower and upper values for the random numbers
=RandArray(3,2,50.75)
Returns random numbers between 50 and 75.
RandBetween() does the same thing for a single cell in other Excel releases.
Integers only
RandArray()’s fifth parameter is a nice addition. It lets you specify just integer results.
FALSE – the default returns numbers with decimal places
TRUE – returns integers, whole numbers only.
=RANDARRAY(5,3,0,999,TRUE)
With Rand() and RandBetween() you need to add a Int() function (e.g. Int(Rand()*100 ) ) to get a whole number between 0 and 99.
Random money values
If you want two decimal place RandArray() results (e.g. for currency) divide each result by 100. For example, random money values up to 100 use the formula
=Int(RANDARRAY(5,2,0,9999,TRUE))/100
That will make random integers from 0 to 9999, each is divided by 100 to get a two decimal placed result.
- Truly random numbers are possible in Excel
- Random Numbers and more in Excel
- Big numbers rounded in Excel
- New Prime Number is too big for Excel
- Benford’s Law and Excel
- El Patron de los Numeros Primos
- Sabotaging Random Numbers
