Run random draw of specific number from list, 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!!

by the way, I have attached an updated spreadsheet with the format of the output that I would like.


Any suggestions would be greatly appreciated. Thanks!

Cross post http://www.excelforum.com/excel-for...variable-and-average-another.html#post3744412