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
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.
&” ” &
INDEX(Table2[Names],RANDARRAY(D1,1,1,COUNTA(Table2[Names]),TRUE))
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
“January”,”February”,”March”,”April”,”May”,”June”,
“July”,”August”,”September”,”October”,”November”,”December”)
- 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((7*3)^2,1,1,COUNTA(staff[Name]),TRUE)),
SEQUENCE(7,3)))
⭐ 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
INDEX(FILTER(Table12[Exercise],Table12[Body Part]=Workout!A2),
INDEX(UNIQUE(RANDARRAY(5^2,1,1,
COUNTA(FILTER(Table12[Exercise],Table12[Body Part]=Workout!A2)),TRUE)),
SEQUENCE(5)),1),””)
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