Monday Morning Mulling: January 2022 Challenge

On the final Friday of each month, we set an Excel / Power Pivot / Power Query / Power BI problem for you to puzzle over for the weekend. On the Monday, we publish a solution. If you think there is an alternative answer, feel free to email us. We’ll feel free to ignore you.

The challenge this month was to randomly select winners from a list without duplicates. Easy, yes?

The Challenge

If you host prize draws on your website, social media, or live meetings, you may need to demonstrate a fair way to select random winner(s).

In this challenge, we required you to create a random selector in Excel to choose three winners for a prize draw from a list of 10 people.  The tricky part here was that a person could not win the game more than once.  Hence, the winners list must not contain any duplicates.

You can download the question file here.

As always, there were some requirements:

• this was a formula challenge; no Power Query / Get & Transform or VBA!
• there must be no duplicate in the result list
• if we changed the number of winners, the formula should still work.

Suggested Solutions

You can find our Excel file here which demonstrates our suggested solutions.  However, before explaining our solutions, we will clarify the common idea how we came up with them first.

Brainstorming

Before picking the winners, we need to shuffle the given list first.  Therefore, an Excel formula that helps generate random numbers is needed.  For example:

• RAND: generates a random number between zero [0] and one [1]
• RANDARRAY: produces an array of random numbers based upon specific conditions
• RANDBETWEEN: returns a random integer between two specified numbers.

Then, we can get a new shuffled list by sorting the old list by random number list.  This can be done using RANK / RANK.EQ or SORTBY.

Finally, we can pick names as many as we need from the new list from the top to bottom.

Returning to the Suggested Solutions

For Excel Office 365, Excel on the Web and Office Beta / Insider versions:

These Excel versions allow us to use Dynamic Arrays which help shorten a lot of steps.

First, we can generate a dynamic list of random numbers using RANDARRAY with the help of ROWS as follows.

Second, in order to shuffle the name list, we use SORTBY function to sort it by random numbers above.

Finally, INDEX and SEQUENCE are used to get first three winners from the new sorted list.

where D11 is the ‘Number of selections’ input cell location.

The resulting list will be a spilled range created by the formula above written in only cell M14 as below:

For all current Excel versions:

As there is no SORT or SORTBY (i.e. Dynamic Array functions) in other Excel versions, we need a helper column using =RAND() to generate a list of random numbers.

Then, we need to find a ranking for each random number above with the help of the RANK function. However, as there is a chance (although very small) that two random numbers created by RAND are the same, RANK will provide the same results for them. For instance, RANK ranks the first two rows as one [1], skips two [2] and continues with three [3].

Hence, we need to modify RANK function using COUNTIF as follows. COUNTIF will help, adding one [1] to any duplicated random numbers that appears subsequently in the helper column.

Then, we can use INDEX within IF function to select three winners with the ranked positions above. These ranked numbers can also be considered as a new random list without duplicates.

The Final Friday Fix will return on Friday 25 February 2022 with a new Excel Challenge. In the meantime, please look out for the Daily Excel Tip on our home page and watch out for a new blog every business working day.