Final Friday Fix: January 2022 Challenge
28 January 2022
On the final Friday of each month, we are going to set an Excel / Power BI challenge for you to puzzle over so that you can get your “Excel fix”. Challenge your office colleagues to see who can solve the puzzle quickest. There are no prizes at this stage: you are playing for bragging rights only!
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).
It is easy to randomly select just one winner as a suggested solution is simple as follows:
=INDEX(List[Name], RANDBETWEEN(1, ROWS(List[Name])))
ROWS counts number of names in the list. RANDBETWEEN will generate a random integer (i.e. position number) between one [1] and the number of names. Then, INDEX will help us pick the name at that position.
In this challenge, the objective is 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 is that a person cannot 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 are some requirements:
- this is a formula challenge; no Power Query / Get & Transform or VBA!
- there must be no duplicate in the result list
- if we change the number of winners, the formula should still work.
Sounds easy? Then why not have a go? We’ll publish one solution in Monday’s blog. Have a great weekend in the meantime!
