generating random integers that are correlated and summed to equal a given value

[iotapsi321]

Hello. I am a novice at excel and have spent more time than I'd like to admit searching for a solution to my question. I want to create a fake dataset for two variables and I alos want to calculate Chronbach's alpha (i.e., reliability score) of those items. I need this information because I will be teaching research methods and I've found that students get a better understanding of stats/methods concepts when they have an applied example.

I want to create a variable that is measured with 6 items (likert-type scale; so a number for each column) with items 1-5 having a range of 1-7 and the 6th item having a range of 1-10. When the items are summed the variable scores can range from 6-45. I would also like to change these values depending on a desired Chronbach's alpha level, such as .7, .2, or even 1.0. The second variable is 12 items than range from 1-5.

I failed miserably at doing this on my own. I tried using the randbetween function and multiplying each value by .7 (a predefined alpha level) and hoped the numbers would come close to a pre-defined summed value. I'm not certain of everyone's expertise so I'll state my problem based on some common language I've seen while searching for a solution. I want to generate random integers in columns A1 - A6 that are intercorrelated so that these integers will equal the number in cell A8.

Sorry for the long post. I wanted to be really clear. Thank you for time.

 

[Kenneth Hobson]

Welcome to the forum!

I have summed random numbers to a set a number in the past. Those were decimal numbers so I would have to look into doing it for integer if you like. I can look for my past example if it will help. In the meantime, an example before and after might help. Free sites like dropbox.com can be used to post a link to the example file.

 

[Kenneth Hobson]

Here is how I did it in the past.

'http://www.ozgrid.com/forum/forum/help-forums/excel-general/145017-generate-an-array-with-positive-integers-that-sum-to-a-specified-value?t=200724
If you want to use formulas for say 5 numbers to sum to 30:

A1:A5 and down: =RAND()
B1:B4: =ROUND(A1/SUM($A$1:$A$5)*30,0)
B5: = 30-SUM(A1:A4)

This method can over round sometimes depending on the sum's value. That can be fixed with a few more formula adjustments.

Copy and paste as values to keep number from changing for calculation events or change calculation mode to manual.

 

[shg]

I want to generate random integers in columns A1 - A6 that are intercorrelated so that these integers will equal the number in cell A8.

Given the constraint that each has a maximum value (5 or 10), it would be easy enough to just iterate through the 5^5 * 10 = 31,250 permutations and pick one at random that meets the desired total.

Then you want to pick another set (one other set?) that has a given alpha to the first one chosen?

Where does the second (12-element) variable come in?

What does "intercorrelated" mean? Does it mean, for example, that if the first three questions are answered "strongly agree" it's unlikely that question 4 would be answered "strongly disagree?"