Generating Random Grades With Correlation

[soundguy2]

I want to generate random grades for 30 hypothetical students in 3 courses, Math, Chem, and English. I can supply a mean and standard deviation and use NORM.INV and RAND() to compute a random grade in each subject for each student.

What I can’t figure out is how to make the grades for each student have some correlation between the subjects. For example, it’s not very realistic that a student will get 90% in Math and 40% in Chem. It’s possible, but it happens way too often when the grades are totally independent. I need a way to correlate the grades so that those with high Math grades generally have high Chem grades, and vice-versa. I want less correlation between Math and English, but the same idea.

Would anybody know of a way to accomplish this type of scenario in Excel? Any help is much appreciated.

 

[Rijnsent]

Hi soundguy,
check out this site: sitmo | Generating Correlated Random Numbers
Given that method, create 3 random sets and use the function Y_1 = rho * X_1 + sqrt(1-rho^2) * X_2 for the 2nd and third data set.
Cheers,
Koen

 

[soundguy2]

Thanks Rijnsent! This is exactly what I was looking for and it does seem to work as desired.

Regards,
Brian