World Population Random Generator

I have a spreadsheet with three columns: the names of 261 countries and territories, their respective population counts and the percentage of the world population total that each nation represents.

I'm trying to make a random generator that will produce the name of one of these countries, where the probability that a particular country's name comes up is equal to the percentage of the world population that it represents. For example, China's population accounts for 18.8617605386887% of the world population, so the odds of my generator producing "China" should be 0.188617605386887.

Haven't been able to get it to work, and I've asked a few friends. Any help?

Use a 4th column to add up those percentages cumulatively, i.e. in D2 use this formula:

=C2+D1

Copy the formula down to the bottom of the list, and put zero in D1.

Then elsewhere you can use this formula:

=INDEX(A:A,MATCH(RAND(),D:D,1))

and copy this down for 10 rows. You can generate another list by pressing F9.

Hope this helps.

Pete

Thanks, Pete!

This solution is closer than anything I've tried, and a lot simplier. But it seems to have a bug in it: the Pitcairn Islands are showing up far, far too often.

Attachment 435124

The Pitcairn Islands have the lowest population of any organized territory in the world - a mere 56 people, accounting for 0.0000000077 of the world population. If the generator assigns probability based on population, it's extremely unlikely that the Pitcairn Islands show up even once in a thousand tries, let alone three times in ten.

I think it's because the Pitcairn Islands are at the end of the list, which is sorted by the B column. Did I make an obvious mistake, or is there some bug in the code?

I can't download your attachment - the system says that it is invalid. Follow the steps given in the FAQ to attach a file, i.e. click on Go Advanced while in edit mode on a post, scroll down and click on Manage Attachments, click on Browse and navigate to where the file is on your PC, then double-click it, click on Upload and Close Window, the Submit Post.

Pete

Thank you. Here is a screenshot of my file:

Screenshot 2015-12-13 at 7.50.11 PM.png

And this is the file itself:

World Populations.xlsx

The bottom value of column D should equal 1, as that is 100% of the population. The fact that it doesn't means there is something wrong, and it is because I thought you would have a header row. I've corrected this and attached a new file. I've amended the formula in E1 to this:

=INDEX(A:A,MATCH(RAND(),D:D)+1)

and copied that down.

Hope this helps.

Pete