random number

[toon]

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

 

[Guest]

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.

 

[Myrna Larson]

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.

 

[Tom Ogilvy]

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

 

[Myrna Larson]

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.

 

[toon]

thank you
antoon

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.

 

[toon]

thank you
antoon

 

[Jerry W. Lewis]

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.aspx?scid=kb;en-us;828795
and use Ian smith's inv_normal function
http://members.aol.com/iandjmsmith/Examples.xls

Jerry

 

[Myrna Larson]

I thought they reworked this stuff in Excel 2003. Are you saying it's worse
than before?

 

[Jerry W. Lewis]

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

 

[Serkan Gur]

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