Array of random numbers in Excel

问题:

How can you create an array of random numbers using the RAND or RANDBETWEEN functions?

I'm trying to simulate the average outcome of 10 rolls of a six sided die (for example) in one formula?

---

I have tried the following in the past, but it only creates one random number and repeats it.

=SUMPRODUCT((ROW(A1:A10)^0)*(INT(RAND()*6)+1))/10

回答1:

The RANDBETWEEN function can deal with array inputs. So

=RANDBETWEEN(ROW(1:10)^0,6)

effectively creates:

=RANDBETWEEN({1;1;1;1;1;1;1;1;1;1},{6;6;6;6;6;6;6;6;6;6})

which returns an array of 10 different random numbers between 1 and 6. This could easily be changed to 100 (or however many) random numbers by changing A1:A10 to A1:A100.

---

Therefore, a single formula for the average of 10 separate dice rolls could be:

=SUMPRODUCT(RANDBETWEEN(ROW(A1:A10)^0,6)/10

or the equivalent CSE formula

{=AVERAGE(RANDBETWEEN(ROW(A1:A10)^0,6))}

回答2:

Three possible solutions:

1) You can do a decent job simulating the average of 10 rolls in a single cell by using a normal approximation. In the target cell enter:

=ROUND(NORM.INV(RAND(),3.5,SQRT(35/(12*10))),1)

I left the 10 in explicitly to show what you would need to modify if you want to vary the number of rolls. The fact that 35 is 3.5 * 10 is a coincidence, the number 35 would stay there even with 100 rolls. If you vary from 10, you might want to tweak the rounding. For 10 the standard deviation of is roughly 0.54, putting 1 and 6 at around 4.6 standard deviations away from the mean of 3.5. On average the formula will give you a value outside the range 1-6 1 out of every 270,000 times. A histogram of dice averages for 10 rolls forms a fairly nice bell-shaped curve, so this approach is certainly reasonable, and might work for your purposes.

2) A VBA sub which fills selected cells with exact spreadsheet formulas, relieving you of the tedious need to count. If there is a requirement that the final spreadsheet is macro-free, this can still be used in the development stage:

Sub DiceAverageFormula()
    'places a formula in the selected cell (or cells)
    'which simulates the average of rolling a die n 10 times

    Dim i As Long, n As Long
    Dim form As String

    n = InputBox("How many times do you want to roll?", "Dice", 10)
    If n < 1 Then Exit Sub
    form = "=Average(Randbetween(1,6)"
    For i = 2 To n
        form = form & ",Randbetween(1,6)"
    Next i
    form = form & ")"
    Selection.Formula = form
End Sub

3) For completeness sake, The naïve VBA UDF:

Function RollDice(n As Long, Optional k As Long = 6) As Double
    'simulates the rolling of n k-sided die, returing the average

    Application.Volatile 'can be removed, of course

    Dim i As Long, sum As Long
    With Application.WorksheetFunction
        For i = 1 To n
            sum = sum + .RandBetween(1, k)
        Next i
    End With
     RollDice = sum / n
End Function

To test the various approaches, I filled 30 rows with each approach and made 5-number summaries of the results (which appear reasonable):

Needless to say, the fact that in this screenshot the mean for the normal approximation is lower than the other two is not particularly relevant. You would need a fairly large number of runs before a statistical test could detect any difference between the first column and the other 2.

On Edit: Here is a graph of exact probability vs. normal approximation for the mean of 10 rolls of a fair die. It underscores how reasonable it is to use the central limit theorem here. Even though n = 10 is a somewhat small sample size, the exact probability is sufficiently symmetric and unimodal that it already looks fairly normal:

回答3:

The only direct answer to the question I can think of is to define a named range called (say) randeval and use the hidden EVALUATE function in its definition:-

 =EVALUATE(REPT("+RANDBETWEEN(1,6)",Sheet1!A1))

Enter 10 in A1, and the following in A2:-

=randeval/a1

Each time you hit Ctrl-Alt-F9, it will produce a new estimate of the average of ten dice rolls.

回答4:

I was trying to do something similar in VBA, but Evaluate doesn't behave quite like Excel formulas:

a = [randbetween(row(1:10)^0,6)]                ' 3

b = [transpose(randbetween(row(1:10)^0,6))]     ' {3,3,6,4,6,4,2,5,1,2}

c = [average(randbetween(row(1:10)^0,6))]       ' 2.9

回答5:

Does this work for you? It has one number of the dice in each row.
You input the number of "throws" in A1 and B column will show you the outcome.

In B1: 
=IF(A$1>=ROW(),RANDBETWEEN(1,6),"")

Then you just fill down to suit your need.

How about a UDF?

Function Throws(c)
    If Range(c).Value > 0 Then
        For i = 1 To Range(c).Value
            Str1 = Str1 & "," & WorksheetFunction.RandBetween(1, 6)
        Next i
        Throws = Mid(Str1, 2)
    End If

End Function

You enter the number of throws in any cell and use =Throws("CELL") to activate it.
And it returns the throws in one cell

EDIT2:

Not sure you can use average even with it being an array.

Function Throws(c) As Variant()
    Dim Str1 As Variant
    If Range(c).Value > 0 Then
        For i = 1 To Range(c).Value
            If i = 1 Then Str1 = WorksheetFunction.RandBetween(1, 6)
            Str1 = Str1 & "," & WorksheetFunction.RandBetween(1, 6)
        Next i
        Throws = Application.WorksheetFunction.Transpose(Array(Str1))
    End If

End Function