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

[abberyfarm]

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

 

[shg]

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

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)

 

[abberyfarm]

Hello Shg,

Thank you for your reply. I think I can adapt your code for my application.

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

 

[shg]

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.

 

[davidzzzz]

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

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?

 

[shg]

Correlated normal variates from different distributions? No.

 

[shg]

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

 

[davidzzzz]

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