My Distributions are Not Correlated Correctly

My problem is that I have been given two distributions of around 5,000 variables that have a correlation of 0 because they are randomly generated numbers. However, I cannot figure out how to alter one of the distributions so that the two distributions have a specific, and changeable correlation.

One option I have found online so far was Cholesky's Decomposition, but I'm not sure how to set this up in excel.

Thanks,

illiniIPA

Your post does not comply with Rule 1 of our Forum RULES. Your post title should accurately and concisely describe your problem, not your anticipated solution.

Use terms appropriate to a Google search. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will be addressed according to the OP's experience in the forum: If you have less than 10 posts, expect (and respond to) a request to change your thread title. If you have 10 or more posts, expect your post to be locked, so you can start a new thread with an appropriate title.

To change a Title go to your first post, click EDIT then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.

(This thread should receive no further responses until this moderation request is fulfilled, as per Forum Rule 7)

I will assume that your amended title is acceptable.

I think you will need to help us understand the math behind what you want to do before we can help you. From what I have gathered in some quick internet searches:

Wikipedia's article talks mostly about using the Cholesky decomposition in solving systems of equations. I cannot readily see how your problem reduces to a "solve a system of equations" kind of problem. If this is how Cholesky's decomposition would apply to your problem, I would probably suggest that you set up your system of equations and then use Excel's built in MINVERSE() function (which uses QR decomposition rather than Cholesky's decomposition) to help solve the system of equations.

If it does not reduce to a system of equations, but the Cholesky decomposition fits into the problem in a different way, generating the Cholesky decomposition should be as "simple" as working through the Gaussian elimination steps to generate the decomposition matrices. These steps can be accomplished in Excel using the MMULT() function.

If the Cholesky decomposition is not an integral part of the solution, I could see using Solver for this sort of thing. Given two data sets, and using the CORREL() function (or whatever function represents your "correlation" score), you could call Solver and tell it to set the CORREL() function cell to a value of -0.05 by changing one or more of the values in the 2nd data set. I expect there are multiple possible solutions to most of these problems. If any solution is acceptable (and you don't need to find a specific solution), this might be the easiest to implement in Excel.