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

abberyfarm

Well-known Member
Joined
Aug 14, 2011
Messages
733
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)
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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?
 
Upvote 0
Correlated normal variates from different distributions? No.
 
Upvote 0
But if you have a link to an algorithm, I'll take a look.
 
Upvote 0

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top