# generating a random number from a normal distribution that is correlated with another number

#### abberyfarm

##### Well-known Member
Hello,

Say you have 2 variables that you know have a pearson's correlation of 0.5.

Say you have a value for one of the variables and you want to generate a random number for the other variable from normal distribution,how could you do this taking into account the correlation?

Appreciate any suggestions

Thanks

### Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.
This will return a pair of numbers with normal distribution and specified correlation:

Code:
``````Function RandNorm(Optional mean As Double = 0, _
Optional Dev As Double = 1, _
Optional Corr As Double = 0, _
Optional bVolatile As Boolean = False) As Double()

' shg 1999-1103

' Returns a pair of random deviates (Doubles) with the specified
' mean, deviation, and correlation.

' Box-Muller Polar Method
' Donald Knuth, The Art of Computer Programming,
' Vol 2, Seminumerical Algorithms, p. 117

Dim z(0 To 1)   As Double
Dim U           As Double
Dim V           As Double
Dim S           As Double

If bVolatile Then Application.Volatile

Do
U = 2# * [rand()] - 1#
V = 2# * [rand()] - 1#
S = U * U + V * V
Loop Until S < 1#

S = Sqr(-2# * Log(S) / S)
z(0) = Dev * U * S + mean
z(1) = Dev * V * S + mean

If Corr <> 0# Then z(1) = Corr * z(0) + Sqr(1# - Corr ^ 2#) * z(1)
RandNorm = z
End Function``````

E.g., select A1:B1 and array-enter

=RandNorm(10, 2, 0.5)

Hello Shg,

You seem to have a good understanding of probability theory.

Would you mind if I ask you a speculative question about copula functions? I understand that this is an excel help forum but I'd be grateful for any comments on a problem that I have.

So I have 4 variables:

1. The time a car departure from home
2. Distance travelled during the day
3. The number of trips made during the day
4. Arrival time back home

Each variable has a different distribution.

I am trying to build the variables's joint distribution using a copula function because they are all correlated to a degree. I am then using this for a Monte Carlo simulation of an individuals daily travel patterns.

Here is the correlation matrix.
https://dl.dropbox.com/u/54057365/All/matrix.JPG
Notice distance is negatively correlated with depart time indicating that the later an individual leaves home the short the distance that they will travel.

However, when I test the significance of all the correlations, I find that one of the correlations is not significantly different from zero (the test statistic is 1.96) .
https://dl.dropbox.com/u/54057365/All/matrix1.JPG
This tells me that arrival time and departure time are not correlated.

My question is: If one of the correlations is not significantly significant can you still use a copula function? because arrival time is correlated with the other 2 variables.

I have been trying to find an example of such a situation online all day but to no avail.

I would greatly appreciate any comments

John

Having learned EVERYTHING I KNOW of copula functions in the last 10 minutes ... I have no idea, John. Interesting problem, though.

I could speck-a-late that if the distributions were independent, the joint cumulative distribution function is linear, which is as valid as any other CDF. Or maybe you just generate those variates independently. But I'm just making that up as I go along -- I really, really have no idea.

However, what about the data itself -- what's the purpose of the trips? If they are people going back and forth to work, then the departure and arrival times would be highly correlated (i.e., people work 9 to 5, or 7 to 3, ...). If it's about people running random errands, then I'd expect departure and return times to be uncorrelated.

This will return a pair of numbers with normal distribution and specified correlation:

Code:
``````Function RandNorm(Optional mean As Double = 0, _
Optional Dev As Double = 1, _
Optional Corr As Double = 0, _
Optional bVolatile As Boolean = False) As Double()

' shg 1999-1103

' Returns a pair of random deviates (Doubles) with the specified
' mean, deviation, and correlation.

' Box-Muller Polar Method
' Donald Knuth, The Art of Computer Programming,
' Vol 2, Seminumerical Algorithms, p. 117

Dim z(0 To 1)   As Double
Dim U           As Double
Dim V           As Double
Dim S           As Double

If bVolatile Then Application.Volatile

Do
U = 2# * [rand()] - 1#
V = 2# * [rand()] - 1#
S = U * U + V * V
Loop Until S < 1#

S = Sqr(-2# * Log(S) / S)
z(0) = Dev * U * S + mean
z(1) = Dev * V * S + mean

If Corr <> 0# Then z(1) = Corr * z(0) + Sqr(1# - Corr ^ 2#) * z(1)
RandNorm = z
End Function``````

E.g., select A1:B1 and array-enter

=RandNorm(10, 2, 0.5)

shg,

This function assumes that the 2 variables have the same distribution (i.e., mean and standard deviation). Correct?

Do you have the macro code for 2 variables with different distributions?

Correlated normal variates from different distributions? No.

But if you have a link to an algorithm, I'll take a look.

Thanks for the offer, shg. I'll do some research and see what I can find.

Replies
2
Views
349
Replies
5
Views
181
Replies
3
Views
212
Replies
4
Views
191
Replies
17
Views
338

1,211,696
Messages
6,103,354
Members
447,861
Latest member
LllopezXC

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back