Categories
BLOG

weighted lottery generator

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.
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. ]]>