My ‘go to’ way to get random numbers in Excel is the new-ish RandArray because can do everything that Rand() or RandBetween() can and more.
RandArray() fills cells with random numbers for as many rows and columns as you want. It’s in Excel 365, Excel 2024 and Excel 2021.
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().
I often use RandArray() to get a column of random numbers like this =RandArray(15,1) drop in 15 numbers down a column.
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
=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.
