Generate random winner based on weighted score

Hi,

We run a monthly competition for our employees based on sales of products. To give everyone a chance to win, they get a point for every item they sell. At the end of the month, we add all the points up, write each person's name down once for each point, randomise the sorting of names in the list, then use another random function to generate a winning number based on the range of the numbers to give a the winning number. It works.

So say we sold 100 items, a person selling 50 items would have a 50% chance of winning something, the person selling 1 item would have a 1% chance.

What I want to do is to create a much simpler way of processing this.

I want to type in the number of units they've sold into a list (see attachment RandomChance.xlsx), this will then show this a percentage of the total sold.

What I'm struggling with is how to produce a weighted random draw with each person having a percentage chance of winning based on their percentage of total units sold. Anyone could win, but your chance of winning is governed by the percentage of the total sales.

Anyone have an idea on how to achieve this?

In a final version, I'll probably use some vba to show a rapid change of numbers to give the illusion of a 'countdown calculation' but this will be a simple recalculation say 9 times to give the 10th random iteration as the winning number.

Thanks in anticipation

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

No need for VBA.
If you SUM your chances (column D) you can use:

Formula:

Please Login or Register  to view this content.

Then you can return name as:

Formula:

Please Login or Register  to view this content.

Rest on the table is check to test that weighted percentage works on over 10.000 numbers.

Proofe is in column F based on COUNTIF (column E) of each name in column H.