2
$\begingroup$

I want to use Excel to generate a random correlated $Y$ from a known $X$. From another thread, I found the equation $Y = r\cdot X + E$, where $X$ is standardized and $E$ is a random variable from normal distribution having mean $0$ and $\sigma = \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.

Improve this question
$\endgroup$
  • $\begingroup$ $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$. $\endgroup$ – Dilip Sarwate Jan 16 '12 at 16:24
  • $\begingroup$ 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. $\endgroup$ – Charles Isaak Jan 16 '12 at 19:54
  • $\begingroup$ 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? $\endgroup$ – Dilip Sarwate Jan 16 '12 at 21:27
  • $\begingroup$ 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. $\endgroup$ – Charles Isaak Jan 17 '12 at 15:28
  • $\begingroup$ From which thread did you find that formula? I would like to have a look. Thanks. $\endgroup$ – qed Sep 1 '13 at 9:14
3
$\begingroup$

If $X \sim N(0, 1)$ and $Y = rX + \epsilon$, where $\epsilon \sim N(0, 1 - r^2)$, then $Cor(X, Y) = r$.

By definition, \begin{align*} Cor(X, Y) &= \frac{E((X - E(X))(Y - E(Y)))}{\sqrt{Var(X)Var(Y)}} \\ &= \frac{E(XY)}{\sqrt{Var(Y)}} \\ &= \frac{E(rX^2 + \epsilon X)}{\sqrt{Var(rX + \epsilon)}} \end{align*}

Assuming $X$ and $\epsilon$ are independent, we have

\begin{align*} Cor(X, Y) &= \frac{rE(X^2) + E(\epsilon)E(X)}{\sqrt{Var(rX) + Var(\epsilon)}} \\ &= \frac{rE(X^2)}{\sqrt{r^2 + 1 - r^2}} \\ &= rE(X^2) \\ \end{align*}

Since $X^2 \sim \chi^2(1)$, we get $Cor(X, Y) = r$.

This can also be verified by a simple simulation in R:

require(foreach)
x = matrix(rnorm(1000*1000), 1000)
err = matrix(rnorm(1000*1000, 0, sqrt(1  - .1^2)), 1000)
myd = (.1*x + err)
allr = foreach(i=1:1000, .combine='c') %do% cor(x[, i], myd[, i])
png('a.png')
hist(allr)
dev.off()

enter image description here

Improve this answer
$\endgroup$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.