Correlation between two different distributions

[theduffman]

I've been working on project and this website and previous forum posts have helped me a number of times, but I've reached the limit of forum searching and googling.

I am using excel to do a monte carlo analysis of a process. Until now, all my distributions have been uncorrelated, but from now I would like to produce distributions with a given correlation. This is a fairly easy process for normal distribution, but in this case one of the distributions is Weibull and the other lognormal. I don't have a stats background, so was hoping someone could help me implement this. Producing the two distributions is no problem, but I would now like to be able to alter them or the original formulae so they are correlated properly. If it would greatly simplify the process, the lognormal distribution could be replaced by a normal, but not the weibull.

The formulae I'm using for each is:

 

[theduffman]

I've been working on project and this website and previous forum posts have helped me a number of times, but I've reached the limit of forum searching and googling.

I am using excel to do a monte carlo analysis of a process. Until now, all my distributions have been uncorrelated, but from now I would like to produce distributions with a given correlation. This is a fairly easy process for normal distribution, but in this case one of the distributions is Weibull and the other lognormal. I don't have a stats background, so was hoping someone could help me implement this. Producing the two distributions is no problem, but I would now like to be able to alter them or the original formulae so they are correlated properly. If it would greatly simplify the process, the lognormal distribution could be replaced by a normal, but not the weibull.

The formulae I'm using for each is:

Sorry, I forgot to include the formula for generating each of the distributions:

Weibull Inverse
"=Scale*(-LN(1-RAND()))^(1/Shape)"

and

Lognormal Inverse
"=LOGNORM.INV(RAND();LN(Mean)-0.5*(LN((StDev/Mean)^2+1));SQRT(LN((StDev/Mean)^2+1)))"

 

[joeu2004]

I am using excel to do a monte carlo analysis of a process. [....] I would like to produce distributions with a given correlation. This is a fairly easy process for normal distribution, but in this case one of the distributions is Weibull and the other lognormal.

[edited] Sorry, I forgot to include the formula for generating each of the distributions:
Weibull Inverse
=Scale*(-LN(1-RAND()))^(1/Shape)
and
Lognormal Inverse
=LOGNORM.INV(RAND();LN(Mean)-0.5*(LN((StDev/Mean)^2+1));SQRT(LN((StDev/Mean)^2+1)))

This is not an area of expertise for me, but....

You seem to be aware of the method, derived from the Cholesky decomposition [click here], for generating two correlated normal distributions x1 and x2 with correlation coefficient R from two uncorrelated normal distributions z1 and z2, to wit:

x1 = z1
x2 = z1*R + z2*sqrt(1+R^2)

I don't know if that applies to non-normal distributions, in general. But since one of your distributions is (log) normally distributed, I would try assigning the Weibull distribution to z1 and the lognormal distribution to z2. For example....

For the Weibull distribution described by scale in S1 and shape in S2, create a random distribution in W1:W10000 using:

=$S$1*(-LN(1-RAND()))^(1/$S$2)

For the lognormal distribution described by mean in M1 and std dev in M2, create a random distribution in L1:L10000 using (as you wrote the formula; I did not vet it):

=LOGNORM.INV(RAND(),LN($M$1)-0.5*LN(($M$2/$M$1)^2+1),SQRT(LN(($M$2/$M$1)^2+1)))

Then create the correlated(?) lognormal(?) distribution in X1:X10000 using:

R1: =CORREL(W1:W10000,L1:L10000)
X1: =W1*$R$1 + L1*SQRT(1+$R$1^2)
Copy X1 into X2:X10000.

Thus, W1:W10000 and X1:X10000 should be correlated with about the same correlation coefficient R1.

TBD: Is X1:X10000 lognormally distributed? If so, is =CORREL(W1:W10000,X1:X10000) approximately equal to R1?

Hope that helps.

PS.... Looking at this algebraically, I suspect it might work for some pairs of uncorrelated distributions, but not for others. I suspect that whether or not X1:X10000 is lognormally distributed depends on which factor is dominant in the X1:X10000 formula: W1*R1 or L1*SQRT(1+R1^2). That would not matter so much if the original assumption were true, namely if both W1:W10000 and L1:L10000 were normally distributed.

 

[theduffman]

Thanks for your help, it really is much appreciated, and I'm in the process of implementing it now. I'll hopefully have something more realistic to implement soon.

As a follow up question, how would approach creating an inverse correlation or a negative correlation (this is also not my area of expertise, and I'm unsure of the correct term.) What I would like to produce is a distribution where a high value of X1 relates to a lower value of X2 and vice versa.

EDIT: I'm now aware that may have been a stupid question, and that using a negative value of R will produce the relationship I want.

 

[joeu2004]

As a follow up question, how would approach creating an inverse correlation or a negative correlation (this is also not my area of expertise, and I'm unsure of the correct term.) What I would like to produce is a distribution where a high value of X1 relates to a lower value of X2 and vice versa.
EDIT: I'm now aware that may have been a stupid question, and that using a negative value of R will produce the relationship I want.

Not so "stupid", IMHO. Yes, you could simply use -ABS(CORREL(...)) for R. But I like to graph the independent distributions together with the correlated distribution. That requires the independent distribution data to actually have a negative correlation.

Errata: In my previous, I referred to "two uncorrelated normal distributions z1 and z2". I should have written "two independent normal distributions".

Perhaps the following example will help. Of course, you need to change the details to apply the ideas to your particulars.

I wanted to create independent normal distributions of periodic changes between -30% and 30%. Although "independent", I wanted to be sure the distributions had a negative correlation.

The most consistently negative correlation is when the signs of %change are opposite. So for my purposes, the formulas were:

A1:A10000: =MAX(-$X$1,MIN($X$1,NORMINV(RAND(),0,$X$1/8)))
B1:B10000: =-SIGN(A1)*ABS(MAX(-$X$2,MIN($X$2,NORMINV(RAND(),0,$X$2/8))))

where X1=30% and X2=30% (they could be different). I used X1/8 because I wanted +/-30% to correspond to +/-8sd, the computational limit of NORMSINV.

That might seem extreme; we might think +/-4sd would suffice. But +/-8sd improves the chances of some extreme values.

If the consistency of negative correlation seems too extreme, we could replace -SIGN(A1) with
IF(RAND()<90%,-SIGN(A1),SIGN(A1)), and we can fine-tune 90% to suit our taste.

Aside.... As you may know, using RAND() causes results to change each time any cell is edited. That can be disconcerting for our purposes. The simplest solution is to copy A1:B10000 and overwrite them using paste-special-value. Alternatives require some VBA code.