Though not a popular dynamic array function, there are many ways to use the RANDARRAY function in Excel.

How come the RANDARRAY function did not and does not get as much fuss. Is it important to learn it?

Even when the excel experts were asked to vote for their favorite functions, RANDARRAY did not show up 😭

Does it then mean that RANDARRAY is a useless function? Is it worth the attention given to its fellow Dynamic array functions?

Generally, the RANDARRAY function returns an array of random numbers just like its predecessor RAND function but you can also specify the range of the random numbers just like in RANDBETWEEN.

Furthermore, unlike RAND & RANDBETWEEN functions, in RANDARRAY you can specify if you want Decimals or Integers.

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

⭐ **[rows] & [columns]** returns the number of rows & columns respectively. If none is selected RANDARRAY returns 1 random number.

⭐⭐**[min] & [max] **specify the range (minimum & Maximum) of random numbers to return. If none is selected, RANDARRAY will return numbers between 0 and 1 respectively.

⭐⭐⭐**[whole_number]** returns a whole number or decimal based on your choice. **TRUE:** Whole Numbers, **FALSE:** Decimals. If not specified it defaults to FALSE

DOWNLOAD WORKSHEET TO FOLLOW ALONG WITH EXAMPLES

Table of Contents

## RANDOMLY SORT A LIST IN EXCEL

Are you fed up with assort ascending or descending?

Today is your lucky day…here is how to sort randomly

The trick here is to use the COUNTA function to return an array equal to the data being sorted length. This is the data that SORTBY Function will use to sort the suppliers

## GENERATE RANDOM NAMES

Generating dummy data for training is one of the taskings and the dreaded job of any trainer.

Here I will show you how to generate random full names.

The trick here is to create a random arrary of Row numbers for INDEX Function to use and return a name.

** COUNTA(Table2[Names]) ** ensures random row numbers returned are within the Lookup table of names

## GENERATE RANDOM DATES

Continuing with the above example, we are going to generate random sales date within the month.

The trick here is just to limit the dates between TODAY() & EndOfMonth

## GENERATE RANDOM TIMES

Since Excel Stores time as a fraction of 24 hours, generating random times is the easiest task to do.

For example 8:00:00 AM = 0.333333333333333 which is 8/24

## GENERATE RANDOM DATETIME

As for the Datetime, excel stores the Date part as a whole number and the time part as the decimal

For example, 11/4/2021 13:49 is stored as 44504.58

## GENERATE RANDOM WEEKDAYS ONLY

The trick here is to wrap RANDARRAY Function with the WORKDAY function.

The WORKDAY function **returns the nearest working day(s) in the future or past**.

## GENERATE RANDOM WORKINGDAYS BETWEEN 2 SPECIFIC DATES

When it comes to generating working days between 2 dates, the WORKDAY function alone cannot do it, you need to include NETWORKDAYS

Here is the formula:

=WORKDAY(startDate,

RANDARRAY( **[rows]**, **[columns]**, 0, NETWORKDAYS(startDate, endDate)-1, TRUE) )

## GENERATE RANDOM LETTERS

CHARACTER (CHAR) function in Excel comes in handy when generating random Capital or lower case letters.

For UPPERCASE letters A-Z CHAR function uses ASCII codes between 65 -90

For lowercase letters, a-z CHAR function uses ASCII codes between 97 -122

## GENERATE RANDOM STRONG PASSWORDS

Do you have a problem generating strong passwords as I do?

Well, worry no more, CHAR Function & RANDARRAY can help you do this

- A strong password usually is at least 8 characters with more characters being better.
- The 8 characters should be a mixture of both uppercase and lowercase letters,numbers and at least one special character, e.g., ! @ # ? ]

**NB: ** To get a combination of Special Character, Numbers, Upper case and lowercase CHAR function uses ASCII-codes between 33 and 125

⭐⭐⭐ The only Trick to learn here is to know **CHAR(RANDARRAY(A2,1,33,125)**) will always give you a random combination of Numbers, Upper & Lower case Letters, and Special characters.

## GENERATE RANDOM MONTHS

For this task, you have to combine the CHOOSE function with RANDARRAY

- Since you don’t want duplicate Months use UNIQUE Functions
- UNIQUE(RANDARRAY(A2,1,1,12,TRUE)) Returns Unique random numbers between 1 and 12
- CHOOSE function returns corresponding value of the returned number

## GENERATING LOTTERY NUMBERS

The chance of winning a lottery is usually infinitesimal but the magnitude of the rewards tends to overshadow it. This is why millions of people play or fantasize about winning the lottery daily.

Since most Lottery uses pseudo-random numbers to pick a winner, I will show you how you can use the RANDARRAY function to boost your chances of choosing the winning number.

For example, Lotto, one of the biggest lottery companies, requires the player to select** 6 numbers** from** 1-49** and a **bonus** from the number** 0-9**.

Human beings cannot consciously generate pseudo-random numbers sequences as they tend to avoid/favor picking certain numbers like Birthdays or anniversaries this is why you need Excel.

## CONDITION FORMATTING RANDOM ROWS

The trick here is to create the Randomly generated rows outside the condition formatting formula

## RANDOMLY FILTER DATA

Using the above example, Instead of condition formatting, you can use FILTER FUNCTION to fetch random rows.

To reduce the constant Spill error you change this Formula** =UNIQUE(RANDARRAY(5,1,1,COUNTA(Suppliers1112[Cost]),TRUE))** for a longer one **=INDEX(UNIQUE(RANDARRAY(5^2,1,1,COUNTA(Suppliers1112[Cost]),TRUE)),SEQUENCE(5))**

A better formula to avoid spill error on large datasets

=INDEX( SORTBY( SEQUENCE(COUNT( Suppliers11[Cost]) ),RANDARRAY( COUNT(Suppliers11[Cost]) ) ), SEQUENCE(5))

Read More about how to generate Random Numbers without repeats and how to avoid the spill range is unknown error

To completely eliminate the Spill# error use a Nested INDEX function

## RANDOMLY ASSIGN GROUPS

For this to happen RANDARRAY must produce random numbers without duplicates

⭐ INDEX(UNIQUE(RANDARRAY( (rows*columns)^2, 1, min, max)), SEQUENCE(rows, columns))…returns an array of random data based on stipulated rows and columns

Alternatively use

**=WRAPROWS(SORTBY(staff[Name],RANDARRAY(ROWS(staff[Name]))),3)**

## RANDOMLY GENERATE ROMAN NUMBERS

=ROMAN(RANDARRAY(n,1, min, max))

## RANDOMLY GENERATE A GYM WORKOUT PLAN

Just for fun and finally, you can let RANDARRAY make for you Meals Plan, Workout Plan, etc

The trick here is just to create a long list of the workout plans and then use INDEX, FILTER, and RANDARRAY to fetch the workout plan.

✔⭐ FILTER(Table12[Exercise],Table12[Body Part]=Workout!A2)…Ensures only the workouts related to the selected dropdown are returned

## SELECT RANDOM WINNER(S)

The trick here is to use the INDEX function to look up data with the RANDARRAY function supplying a random row number.

## References:

- https://www.youtube.com/watch?v=Vl-0LJp7Zis&t=4s&ab_channel=ExcelOffTheGrid

2. https://www.youtube.com/watch?v=RikzaFoo2Gc&ab_channel=ExcelIsFun

3. https://exceljet.net/excel-functions/excel-randarray-function

4. Generate Unique Random Groups :

5. https://www.youtube.com/watch?v=9tpgVPmLE4o&ab_channel=ExcelOnFire

6. https://www.get-digital-help.com/how-to-use-the-randarray-function/

7. https://www.ablebits.com/office-addins-blog/2020/07/15/excel-randarray-function-generate-random-numbers/

8.https://www.fm-magazine.com/news/2018/dec/excel-functions-sequence-randarray-201820101.html

## Recent Comments