Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
random number
i want to recall a random number out of a statistic sample, with a certain
mean & variance fe if i repeat 100 x the question, i would like to have a representative universe can anybody help me? thanks a lot and sorry for my english antoon |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
random number
Hi Toon,
Not certain this will do exactly what you want: No S (No is a numbering of your sample S, hidden 3 tru 98) 1 1 (S is your sample result, used 1 to 100 for simplicity, obvious yours 2 2 is differant) 99 99 100 100 Random1 98 =VLOOKUP(RANDBETWEEN(1,100),$A$1:$B$101,2,FALSE) Random2 47 Random99 84 Random100 69 =VLOOKUP(RANDBETWEEN(1,100),$A$1:$B$101,2,FALSE) (The Vlookup pulls down from above your sample data, why you needed to number the sample) Median 56.5 =MEDIAN(B103:B203) Variance 849.2690909 =VAR(B103:B202) Hope this helps and does what you want. "toon" wrote: i want to recall a random number out of a statistic sample, with a certain mean & variance fe if i repeat 100 x the question, i would like to have a representative universe can anybody help me? thanks a lot and sorry for my english antoon |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
random number
If I understand correctly, you have a normally distributed population with a
specified mean M and variance V. Standard deviation, S, equals SQRT(V) If you put this formula in A1:A100, =NORMINV(RAND(),M,S) the resulting 100 values should be normally distributed with mean ~= M and standard deviation ~= S. On Sun, 20 Feb 2005 20:49:24 +0100, "toon" wrote: i want to recall a random number out of a statistic sample, with a certain mean & variance fe if i repeat 100 x the question, i would like to have a representative universe can anybody help me? thanks a lot and sorry for my english antoon |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
random number
What distribution - Normal?
Myrna Larson: Function RandomNormal(Optional Mean As Double = 0, _ Optional SD As Double = 1) As Double 'This function is to enable to use normal distrubtion vs the Rnd ' function which is uniform 'This function can be called if a uniform distrubution is not ' warranted Static HaveX1 As Boolean Dim V1 As Double, V2 As Double, S As Double Static X1 As Double Dim X2 As Double If HaveX1 = False Then Do V1 = Rnd() * 2 - 1 V2 = Rnd() * 2 - 1 S = V1 * V1 + V2 * V2 Loop While S = 1# Or S = 0# S = Sqr(-2 * Log(S) / S) X1 = V1 * S X2 = V2 * S HaveX1 = True RandomNormal = X2 * SD + Mean Else HaveX1 = False RandomNormal = X1 * SD + Mean End If End Function 'RandomNormal -- Regards, Tom Ogilvy "toon" wrote in message ... i want to recall a random number out of a statistic sample, with a certain mean & variance fe if i repeat 100 x the question, i would like to have a representative universe can anybody help me? thanks a lot and sorry for my english antoon |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
random number
Yeah, I wrote that before Mike Middleton (I think it was) pointed out the
simple worksheet formula using NORMINV and RAND(). Of course there are those bugs in NORMINV that may cause problems. On Sun, 20 Feb 2005 15:50:53 -0500, "Tom Ogilvy" wrote: What distribution - Normal? Myrna Larson: Function RandomNormal(Optional Mean As Double = 0, _ Optional SD As Double = 1) As Double 'This function is to enable to use normal distrubtion vs the Rnd ' function which is uniform 'This function can be called if a uniform distrubution is not ' warranted Static HaveX1 As Boolean Dim V1 As Double, V2 As Double, S As Double Static X1 As Double Dim X2 As Double If HaveX1 = False Then Do V1 = Rnd() * 2 - 1 V2 = Rnd() * 2 - 1 S = V1 * V1 + V2 * V2 Loop While S = 1# Or S = 0# S = Sqr(-2 * Log(S) / S) X1 = V1 * S X2 = V2 * S HaveX1 = True RandomNormal = X2 * SD + Mean Else HaveX1 = False RandomNormal = X1 * SD + Mean End If End Function 'RandomNormal |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
random number
thank you
antoon "Myrna Larson" schreef in bericht ... Yeah, I wrote that before Mike Middleton (I think it was) pointed out the simple worksheet formula using NORMINV and RAND(). Of course there are those bugs in NORMINV that may cause problems. On Sun, 20 Feb 2005 15:50:53 -0500, "Tom Ogilvy" wrote: What distribution - Normal? Myrna Larson: Function RandomNormal(Optional Mean As Double = 0, _ Optional SD As Double = 1) As Double 'This function is to enable to use normal distrubtion vs the Rnd ' function which is uniform 'This function can be called if a uniform distrubution is not ' warranted Static HaveX1 As Boolean Dim V1 As Double, V2 As Double, S As Double Static X1 As Double Dim X2 As Double If HaveX1 = False Then Do V1 = Rnd() * 2 - 1 V2 = Rnd() * 2 - 1 S = V1 * V1 + V2 * V2 Loop While S = 1# Or S = 0# S = Sqr(-2 * Log(S) / S) X1 = V1 * S X2 = V2 * S HaveX1 = True RandomNormal = X2 * SD + Mean Else HaveX1 = False RandomNormal = X1 * SD + Mean End If End Function 'RandomNormal |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
random number
thank you
antoon "toon" schreef in bericht ... i want to recall a random number out of a statistic sample, with a certain mean & variance fe if i repeat 100 x the question, i would like to have a representative universe can anybody help me? thanks a lot and sorry for my english antoon |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
random number
If you want some rather problematic issues with accuracy and randomness,
do the NORMINV thing in 2003 with the latest patches, or else program the 2003 generator http://support.microsoft.com/default...b;en-us;828795 and use Ian smith's inv_normal function http://members.aol.com/iandjmsmith/Examples.xls Jerry Myrna Larson wrote: Yeah, I wrote that before Mike Middleton (I think it was) pointed out the simple worksheet formula using NORMINV and RAND(). Of course there are those bugs in NORMINV that may cause problems. On Sun, 20 Feb 2005 15:50:53 -0500, "Tom Ogilvy" wrote: What distribution - Normal? Myrna Larson: Function RandomNormal(Optional Mean As Double = 0, _ Optional SD As Double = 1) As Double 'This function is to enable to use normal distrubtion vs the Rnd ' function which is uniform 'This function can be called if a uniform distrubution is not ' warranted Static HaveX1 As Boolean Dim V1 As Double, V2 As Double, S As Double Static X1 As Double Dim X2 As Double If HaveX1 = False Then Do V1 = Rnd() * 2 - 1 V2 = Rnd() * 2 - 1 S = V1 * V1 + V2 * V2 Loop While S = 1# Or S = 0# S = Sqr(-2 * Log(S) / S) X1 = V1 * S X2 = V2 * S HaveX1 = True RandomNormal = X2 * SD + Mean Else HaveX1 = False RandomNormal = X1 * SD + Mean End If End Function 'RandomNormal |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
random number
I thought they reworked this stuff in Excel 2003. Are you saying it's worse
than before? On Sat, 05 Mar 2005 05:59:10 -0500, "Jerry W. Lewis" wrote: If you want some rather problematic issues with accuracy and randomness, do the NORMINV thing in 2003 with the latest patches, or else program the 2003 generator http://support.microsoft.com/default...b;en-us;828795 and use Ian smith's inv_normal function http://members.aol.com/iandjmsmith/Examples.xls Jerry Myrna Larson wrote: Yeah, I wrote that before Mike Middleton (I think it was) pointed out the simple worksheet formula using NORMINV and RAND(). Of course there are those bugs in NORMINV that may cause problems. On Sun, 20 Feb 2005 15:50:53 -0500, "Tom Ogilvy" wrote: What distribution - Normal? Myrna Larson: Function RandomNormal(Optional Mean As Double = 0, _ Optional SD As Double = 1) As Double 'This function is to enable to use normal distrubtion vs the Rnd ' function which is uniform 'This function can be called if a uniform distrubution is not ' warranted Static HaveX1 As Boolean Dim V1 As Double, V2 As Double, S As Double Static X1 As Double Dim X2 As Double If HaveX1 = False Then Do V1 = Rnd() * 2 - 1 V2 = Rnd() * 2 - 1 S = V1 * V1 + V2 * V2 Loop While S = 1# Or S = 0# S = Sqr(-2 * Log(S) / S) X1 = V1 * S X2 = V2 * S HaveX1 = True RandomNormal = X2 * SD + Mean Else HaveX1 = False RandomNormal = X1 * SD + Mean End If End Function 'RandomNormal |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
random number
No, I was trying to say use either 2003 or ... I appear to have omitted
the "to avoid" in "If you want to avoid some ...". Sorry. However, in 2003 one should install the service patch from March 2004, which fixed the problem of the (otherwise much improved) RAND() worksheet function returning negative values. Also, recall that Worksheet, VBA, and ATP random number generators are separate entitites. Only the worksheet RAND() function was improved in 2003. Jerry Myrna Larson wrote: I thought they reworked this stuff in Excel 2003. Are you saying it's worse than before? On Sat, 05 Mar 2005 05:59:10 -0500, "Jerry W. Lewis" wrote: If you want some rather problematic issues with accuracy and randomness, do the NORMINV thing in 2003 with the latest patches, or else program the 2003 generator http://support.microsoft.com/default...b;en-us;828795 and use Ian smith's inv_normal function http://members.aol.com/iandjmsmith/Examples.xls Jerry Myrna Larson wrote: Yeah, I wrote that before Mike Middleton (I think it was) pointed out the simple worksheet formula using NORMINV and RAND(). Of course there are those bugs in NORMINV that may cause problems. On Sun, 20 Feb 2005 15:50:53 -0500, "Tom Ogilvy" wrote: What distribution - Normal? Myrna Larson: Function RandomNormal(Optional Mean As Double = 0, _ Optional SD As Double = 1) As Double 'This function is to enable to use normal distrubtion vs the Rnd ' function which is uniform 'This function can be called if a uniform distrubution is not ' warranted Static HaveX1 As Boolean Dim V1 As Double, V2 As Double, S As Double Static X1 As Double Dim X2 As Double If HaveX1 = False Then Do V1 = Rnd() * 2 - 1 V2 = Rnd() * 2 - 1 S = V1 * V1 + V2 * V2 Loop While S = 1# Or S = 0# S = Sqr(-2 * Log(S) / S) X1 = V1 * S X2 = V2 * S HaveX1 = True RandomNormal = X2 * SD + Mean Else HaveX1 = False RandomNormal = X1 * SD + Mean End If End Function 'RandomNormal |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
random number
Here is a very easy and relatively fast way to generate normalized random numbers good enough to use in MonteCarlo simulations etc. : Function Nrand() Nrand=Rnd+Rnd+Rnd+Rnd+Rnd+Rnd+Rnd+Rnd+Rnd+Rnd+Rnd+ Rnd-6 End Function Regards, Serkan *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Addding a Random number to a fixed number..... | Excel Discussion (Misc queries) | |||
How can I match a random number with closest number from sequence? | Excel Worksheet Functions | |||
same number appears in a random number generator | Excel Worksheet Functions | |||
Generating (in a random order)each number once from a given number | Excel Worksheet Functions | |||
How do I find random number in list of random alpha? (Position is. | Excel Discussion (Misc queries) |