Introduction
Imagine any scenario in which we need to assign participants, employees, students, anyone to a group. However, you want to do that randomly rather then just by their every day group assignment number of employee ID number and so on. This page shows you two methods
Using Dynamic Array Functions
PROVIDING YOU HAVE EXCEL 365, use this formula in a cell =SORTBY(B5:B13,RANDARRAY(9,,0,1)). This uses the SORTBY() and RANDARRAY() dynamic array functions to great effect.
Where your names are in the range B5:B13 and your group numbers are in the column to the left of this formula, as you see below
That is all you have to do although you don’t need to create the second Group column, I did that just for demonstration. There is nothing wrong with doing that if you want to, of course.
If you do not have Excel 365
If you do not have Excel 365, the screenshot below shows you what to do
In this case, I have copied the Group column but I don’t need to, it is there to help you to understand.
I copied the Name list: in this case, I had to do that
I created the Random column based on the RAND() function you see
Then I sorted the Name and RAND() columns … Data … Sort … by the Random column and it does not matter whether you sort by smallest to largest or largest to smallest
Clearly, you need to reorganise your worksheet according to your own situation but these two approaches work perfectly.
Duncan Williamson
4th October 2022
No comments:
Post a Comment