Randomly Choosing a Winner from a Weighted List with Excel
Quote of the Day
Fashion is architecture: it is a matter of proportions.
— Coco Chanel. I hold the same view of product design. There is a balance that must be achieved between price, features, and schedule.
Figure 1: Randomly Choosing a Winner. (Source)
My wife is participating in a friendly contest at work that encourages employees to exercise. The employees record how many laps they walk around a set course during a month. At the end of the month, an Excel “drawing” is held to award a prize to one of the exercisers. To encourage more exercising, the likelihood of winning is to be proportional to the number of laps each person walked during the month. I was asked if I could write an Excel worksheet that would perform this task. I thought it was an interesting spreadsheet that was worth sharing here. There is a simple macro in the worksheet that controls when the worksheet re-calculates (otherwise it re-calculates a winner every time you change anything on the worksheet).
To make things simple for the users, the worksheet has the following features:
- It uses a table that allows my wife to add people as new participants arrive with no change in the random chooser.
- No “helper” columns are used, which are confusing to some folks.
- The key calculation uses an array formula that generates a cumulative sum, which is a useful thing to have in your toolbox.
- When you press the count button, it actually performs the randomizing six times. I wanted to make sure I avoided any random number start-up issues.