Choose Option Based on Percentage

Please upload a workbook as it is a little confusing.

Go Advanced>Manage Attachments. 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 then scroll down to Manage Attachments to open the upload window.

Let's say you have the percentages in F1:F5 (35%, 20%, 15%, 20%, and 10% ) and the corresponding race in G1:G5. Replace the percentages with the Running Totals.(0%,36%,56%,71%,91%). You can use this formula to randomly (within given percentiles) select race.

=VLOOKUP(RANDBETWEEN(1,100), $F$1:$G$5,2)

Okay, I placed the formula that I mentioned in post 2 into your worksheet. Also in Columns E:G, I put the actual percentages based on picking 10 subjects, 30 subjects and 100 subjects (randomly). Theoretically, the more subjects you have, the closer the result will be to the entire population. Hitting F9 (shortcut for "recalculate") will change your results if you are looking to get values close to whole population with only 10 subjects.

Questions?

Could you explain the concept behind the formula? For instance what it is actually doing in order? I still find myself a bit confused on what it is actually doing behind the scenes.

Also, is there a way to stop it from recalculating when I change something else in the workbook? And at what population would you say that it would come close to the theoretical population %s? I tried it out at 1000, but some of the %s were still pretty far away, so is it based on a distribution around a %?

And I'm uncertain where the running totals came from.

Okay, starting with
=VLOOKUP(RANDBETWEEN(1,100), $A$2:$B$6,2)
RANDBETWEEN(1,100) is randomly choosing a number from 1 to 100. Every number has an equal chance of being chosen.

Then it looks up this number in the table A2:B6 returning a value from the second column. The VLOOKUP looks down column A until it finds a value larger than the number (or comes to the end). Then looks across to pull the value from Col B.
So there are 15 numbers between 86 and 100 or a 15% chance that the value will correlate to "Other". Between 1 and 30 there's a 30% chance of the number correlating to "White" and so forth.

I would expect at 1000, the percentages would be pretty close to reality. Are you sure you modified your COUNTIF formulas properly?

If you want everything to exactly fall into each bucket percentage wise then you're no longer truly random. It is easy to do that in Excel though

To stop the Random values from recalculating each time you change something, set Calculations to Manual (Calculation Options on the Formulas Tab of the ribbon).

I set up a sheet that will automatically match the number of subjects exactly to the demographics. Fractions of people will be placed in "Other"

1 In G1, I entered the # of people in the study (20)
2 In G2:G5, =INT($G$1*F2/100)
3 In G6 (Other) = G1-SUM(G2:G5)

4 Enter the Subject numbers into C2 down
5 Enter the formula =RAND() into A2:A21

6 In B2 copied down is this formula
=INDEX($E$2:$E$6, MATCH(ROW()-1, $H$2:$H$6))
which enters the appropriate number of each race.

7 Next Copy Columns A and B and Paste Special Values (in place) to remove the formulas.
8 Select data in Column A and B and Sort by A (Do not Extend selection if prompted)

Sheet2 shows sheet before step 7 (formulas intact)
Sheet 3 shows sheet after step 8.