# New Improved RANDARRAY Function Coming Soon to Office 365 Excel

6 February 2019

OK, so this is still in what Microsoft refers to as “Preview” mode, *i.e. *it’s not yet “Generally Available” but it is on the outskirts of civilisation. **RANDARRAY **is still a relatively new function found in some editions of the “Office Insider” programme which is an Office 365 fast track. You can register in **File -> Account -> Office Insider **in Excel’s backstage area.

Even then, you’re not guaranteed a ticket to the ball as only some will receive the new features as Microsoft slowly roll out these features and functions. Please don’t let that put you off. These features will be with all Office 365 subscribers soon.

We first mentioned **RANDARRAY **back in September. Even though it’s not yet Generally Available, it’s already had a facelift. Oh yes – Microsoft is invested in these functions!

Originally, the **RANDARRAY** function returned an array of random numbers between 0 and 1. It’s not clear from Microsoft, analogous to the pre-existing **RAND** function, which generates a number greater than or equal to zero and strictly less than one. However, there was a general sense of underwhelm with this function and the new and improved version has just been released. It now allows you to set you own maximum and minimum *and* decide whether you want the values returned to be decimals (*e.g. *17.4381672…) or integers (whole numbers).

The new syntax for the function is now as follows:

**=RANDARRAY([rows], [columns],[min],[max],[integer])**.

The function has five arguments, all supposedly optional (but upon testing, we weren’t quite as convinced):

**rows:**this specifies how many**rows**the results should spill over. If omitted, the default value is 1**columns:**this specifies how many**columns**the results should spill over. If omitted, the default value is also 1**min:**this is the minimum value that may be selected randomly. If this is not specified, it is assumed to be zero (0)**max:**this is the maximum value that may be selected randomly. If this is not specified, it is assumed to be 1**integer:**if this is set to TRUE, only integer outputs are allowed; the default value (FALSE) provides non-integer (decimal) results.

Other points to note:

- if
**rows**or**columns**refers to a blank cell reference, this will generate the new*#CALC!*error - if
**rows**or**columns**are entered as decimals, the values used will be truncated to the number before the decimal point (*e.g.*3.999 will be treated as 3 digits) - if
**rows**or**columns**is a value less than 1,*#CALC!*will be returned - if
**integer**is set to TRUE and either**min**or**max**is not an integer, this will generate an*#VALUE!*error **max**must be greater than or equal to**min**, else the error*#VALUE!*is returned.

When we originally discussed the **RANDARRAY **function, we used this rather comprehensive example to create a list of random integers between two values:

Originally, the formula in cell **F44** was

**=ROUNDDOWN(RANDARRAY(H36,H37)*(H39-H38+1),0)+INT(H38)**

and the article explained how this worked. However, it’s much easier now:

The “new improved” formula in cell **F45 **(it’s moved down a row due to the additional argument required in cell **H40**) is simply

**=RANDARRAY(H36,H37,H38,H39,H40)**.

Cool, eh?