Populate Data set on Mean Standard Deviation and Correlation

I'll start with two general comments:
- as you are looking for correlation it does not matter which variable is dependant :mrgreen:
- you wisely used NormInv - but have you tested the results? You have relatively small sample (30 items), so generated data are usually noticeably far from expected mean/std - they would probably pass the statistical tests but it does not look nicely. try

Formula:

Please Login or Register  to view this content.

and few <F9> strokes - quite a spread isn't it?

And below is my vision how to do it:

First - generate two normally distributed variables, letting Excel to repeat generation until mean and stddev are pretty close to these listed in upperleft corner
Second: do special sorting of data - with both random and data correlation influence.

The first part I adopted one of procedures I used some time ago. The second part is done just for this task, so I made it quick and dirty way - by macro using the spreadsheet for almost whole "calculation engine" and all adresses hardcoded in macro.

The code is as follows (you can use it in your file or test it first in attached one):

Please Login or Register  to view this content.

Not tested extensively, but shall do the work. Note that it is not effective way to generate sets of high (either negative or positive) correlation like 0.9 or -0.9, not to mention -1 or 1 . If there would be such a need, at least one variable shall be generated several times in an inner loop. Something similar to:

Please Login or Register  to view this content.

After tiny changes almost the same code can be applied.
Just changed obvious addresses because layout is now different, moved DEFS location leftmost (to facilitate easy sorting of other variables), moved parts to sort generated DEFS in ascending order before generation of other parameters and in random order after generation from subroutine to main unit. This was key modification, to keep every next variable fitted (in sense of correlation coefficient) to DEFS without disturbing correlation acheved between DEFS and previus variables).
As I added parameter to call SpecialSort in appropriate column, I made it public (previously was Private not to be visible in Macro: Alt-F8 dialog).

In the worksheet I added regression coefficient in row 81 for each pair (row 79 and 80 Avg and stdev). That's it. So few minor changes and it works. See attachment.

Please Login or Register  to view this content.