Random distribution of numbers...

Hi folks,

I've 7 numbers (-3 to +3) that I need to distribute randomly (as best we can) 2539 times between other entries.

If possible, as close as we can to the following weights... This is the best way I could phrase it...

-3	-2	-1	0	1	2	3
0.05	0.1	0.2	0.3	0.2	0.1	0.05

Any ideas? I'm sure I've seen a post on here similar to this, and I've tried to mod the following code to suit my needs, but I can't quite get it to work, I think because of the duplicated weights...

=CHOOSE(MATCH(RAND(),{0,0.44,0.6,0.71},1),"A","B","C","D")

Bumpy bump, even a guess could be helpful here folks, if anyone has even a ballpark idea.... fire away....

Hi,
This is a complicated one, but try....
{=INDEX(List,LARGE((ROW(List)-MIN(ROW(List))+1)*(COUNTIF($D$2:D2,List)<>COUNTIF(List,List)),RANDBETWEEN(1,SUM(--(COUNTIF($D$2:D2,List)<>COUNTIF(List,List))))))}
It does work!

I'm not entirely sure of just what you want.

But here's a VBA code that enters your numbers randomly in Range(A1:A2529), with relative frequency according to your indicated weights. Just run it on a blank worksheet and see.

Sub random_distr()

Const n& = 2529
Dim vals, probs
Dim c As Long, j As Long
Dim s As Double, x As Double
Dim d As Object

Set d = CreateObject("scripting.dictionary")
vals = Array(-3, -2, -1, 0, 1, 2, 3)
probs = Array(0.05, 0.1, 0.2, 0.3, 0.2, 0.1, 0.05)

For c = 1 To n
    s = 0: x = Rnd
    For j = 0 To UBound(probs)
        s = s + probs(j)
        If x <= s Then
            Cells(c, "a") = vals(j)
            d(vals(j)) = d(vals(j)) + 1
            Exit For
        End If
Next j, c

[c1].Resize(, 3) = Array("Number", "Count", "Frequency")
With [c2].Resize(d.Count, 2)
    .Value = Application.Transpose(Array(d.keys, d.items))
    .Sort [c2], 1, Header:=xlNo
End With
With [d2].Resize(d.Count)
    .Offset(, 1) = Evaluate(.Address & "/" & n)
    .Offset(, 1).NumberFormat = "0.00"
End With

End Sub