# Generate random correlated variable from known $X$

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.

• $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
• From which thread did you find that formula? I would like to have a look. Thanks. – qed Sep 1 '13 at 9:14

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()