Tell me more ×
Cross Validated is a question and answer site for statisticians, data analysts, data miners and data visualization experts. It's 100% free, no registration required.
up vote 0 down vote favorite

I want to use Excel to generate a random correlated Y from a known X. From another thread, I found the equation Y = r * X + E, where X is standardized and E is a random variable from normal distribution having mean 0 and sd = sqrt(1-r^2). I assume r is the correlation coefficient found using Excel's CORREL function. I also assume I can calculate E by using Excel's NORMDIST function.

Are my assumptions correct? If I have a known X, how do I "standardize" X?

Thanks for any help.

share|improve this question
$r$ is the correlation that you want $X$ and $Y$ to have, not something computed via Excel. A standardized $X$, call it $\hat{X}$, is related to $X$ via $$\hat{X} = \frac{X - \mu}{\sigma}$$ where $\mu$ is the mean value of $X$ viz.,the average of the $N$ cells if $X$ is stored in an array of $N$ cells, and $\sigma$ is the standard deviation of the $N$ values of $X$. $\hat{X}$ has mean $0$ and standard deviation $1$. Your equation thus is $$Y = r * \hat{X} + E,$$ and $Y$ is also a standardized random variable with mean $0$ and standard deviation $1$. $aY+b$ also has correlation $r$ with $X$. – Dilip Sarwate Jan 16 '12 at 16:24
So is this the equation? (Y-meanY)/sdY = r * (X-meanX)/sdX + E where E is a random variable from a normal distribution with mean 0 and sd sqrt(1-r^2)? Still confused as to what r is in my example. – Charles Isaak Jan 16 '12 at 19:54
Yes, your equation is correct. As to $r$, you need to look at the specifications given to you when you were told "Create a random variable $Y$ that is correlated with $X$". The statement should have included a specification of $r$ e.g. "... that has correlation $r = 0.8$ with $X$". If your client/professor/boss/colleague did not say what value of $r$ is desired, ask! $r$ should be between $-1$ and $+1$. All else failing, set $r=\sqrt{1-r^2}=1/\sqrt{2} \approx 0.7071$ because I said to do so. Hey, if you can't trust something you read on the Internet, what's the world coming to? – Dilip Sarwate Jan 16 '12 at 21:27
Thanks. I think I am close now. Here is the equation I am using: Y = (((r*((actualX-meanX)/stdX))+RN)*stdY)+meanY, where RN = a random normal variable with mean 0 and std of sqrt(1-r^2). However, I am still confused about r. This is not an assignment so no one is giving me a target correlation. My goal remains to generate the most accurate possible random Y from a known X using what I have found from regression analysis. When using the above formula, the generated Ys are highly affected by r so it seems to be important to use a proper r. – Charles Isaak Jan 17 '12 at 15:28

Know someone who can answer? Share a link to this question via email, Google+, Twitter, or Facebook.

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.