RandArray – Excel’s new way to get random numbers

Office for Mere Mortals
Your beginners guide to the secrets of Microsoft Office
Invalid email address
Tips and help for Word, Excel, PowerPoint and Outlook from Microsoft Office experts.  Give it a try. You can unsubscribe at any time.  Office for Mere Mortals has been running for over 20 years, we've never, ever revealed or sold subscriber details.  Privacy policy

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.

Latest news & secrets of Microsoft Office

Microsoft Office experts give you tips and help for Word, Excel, PowerPoint and Outlook.

Give it a try. You can unsubscribe at any time.  Office Watch has been running for over 20 years, we've never, ever revealed or sold subscriber details.  Privacy policy
Invalid email address