Formula to run random draw of specific number, then sum one variable and average another??

I am completely stumped on how to solve my problem. Hopefully someone here will know just the trick :-)

I have data for groups of different sizes and I need to re-create these group characteristics using aggregate data from individuals.

Specifically, I need to randomly select different numbers of individual level "Person IDs" (based on the group size) and then sum the values of variable X across those randomly selected people. Also, I need to average the values of variable Y across those randomly selected people.

This sounds a bit confusing so I have attached a sample spreadsheet that clearly outlines my problem.

I need to do this procedure for thousands of rows of data and would love to be able to automate it.

Thanks so much in advance for your help!!! I really appreciate it!!

Also crossposted here:

http://www.mrexcel.com/forum/excel-q...n-random-draw-
specific-number-changes-then-sum-one-variable-average-another-across-randoml
y-selected-values.html#post3850066


http://www.ozgrid.com/forum/showthre...493#post717493

http://www.excelguru.ca/forums/showt...draw-of-specif
ic-number-from-list-sum-one-variable-and-average-another&p=13545#post13545


Attachment 327596Excel example2.xlsx

Hi,

It's still confusing, at least to me. You have an output sheet with no values.

In addition you mention that you need to select a maximum of 20 IDs yet I only see 5 IDs. As a minimum we'd like to see a list of say 100 IDs and an output sheet which clearly shows what results you expect along with notes that explain how you arrived at the results.

Well, you don't give a very large table, but your table should be on a separate sheet. You need a column of random numbers: to not always select the same people, a macro re-calcs and pastes values next to the formula, one time for each row.

Excel example2.xlsm

All macro, no formula...

example REVISED.xlsm

I have added comments, and the extra four lines required for the extra two columns.... I think you can figure out the pattern in the code.

data_add_variables.xlsm

Your post does not comply with Rule 8 of our Forum RULES. Do not crosspost your question on multiple forums without including links here to the other threads on other forums.

Cross-posting is when you post the same question in other forums on the web. The last thing you want to do is waste people's time working on an issue you have already resolved elsewhere. We prefer that you not cross-post at all, but if you do (and it's unlikely to go unnoticed), you MUST provide a link (copy the url from the address bar in your browser) to the cross-post.

Expect cross-posted questions without a link to be closed and a message will be posted by the moderator explaining why. We are here to help so help us to help you!

Read this to understand why we ask you to do this, and then please edit your first post to include links to any and all cross-posts in any other forums (not just this site).

http://www.excelguru.ca/forums/showt...erage-another&