If you are going to go to the trouble of generating that many 5-tuple
selections, you might as well generate all selections of 5 out of 14.
There are only 2002.
[....]
Or simply sort the 2002 data (averages of 5)....
[....]
....And count the number greater than the reference value.
See the VB sub gendata below. It's a hack. Skilled VB programmers
might offer improvements.
Usage: create a new worksheet called "data". Then highlight the
range with the 14 numbers in the original worksheet; these must be
horizontal (i.e. part of a row). Then execute the macro. The
probability that the average of 5 random numbers out of the 14 is
greater than "x" is (replace "x" with a number):
=match(x, data!$a1:$a2002) / 2002
The macro is fast enough. But as it turns out, 90+% of the time is
spent in sorting the resulting array. In this case, it is faster to
use a function, even if we recreate the 2002 possible averages each
time. (Programming note: that could be avoided by storing into a
Public variable the first time.)
See the VB function genprob below. Usage: first argument is "x";
second argument is the 14-number range (again, horizontal).
In that case it would be useful to know the min and max 5-tuple
averages. See the VB function minmax below. Usage: first argument
is -1 for min, 1 for max; second argument is the 14-number range
(horizontal).
But the VB sub gendata is useful because it "returns" the entire array
of 2002 averages. That allows us to do some statistical analysis.
For example, use the Excel Histogram add-in (Data Analysis) and Chart
Wizard to graph the distribution of the averages. You will discover
that it is a normal distribution.
That is an important discovery. It demonstrates a principle of the
Central Limit Theorem that can be used to develop a more general
statistical solution. If this is for a class in statistics and
probability, I suspect that is what your instructor wants you to use
for the solution.
Obviously, more needs to be said. But I have run out of time. I'll
try to post back later. But this is best explained by the likes of
Jerry Lewis and Mike Middleton, if they are reading this thread.
-----
Sub gendata()
Dim pop As Variant
Dim x(1 To 2002) As Double
Dim i1 As Integer, i2 As Integer, i3 As Integer, i4 As Integer, i5 As
Integer
Dim i As Integer, j As Integer, k As Integer
Dim t As Double
Dim n As Long
If Selection.Count <> 14 Then Exit Sub
pop = Selection.Value
If UBound(pop, 1) <> 1 Then Exit Sub ' must be horizontal
n = 0
For i1 = 1 To 10
For i2 = i1 + 1 To 11
For i3 = i2 + 1 To 12
For i4 = i3 + 1 To 13
For i5 = i4 + 1 To 14
n = n + 1
x(n) = (pop(1, i1) + pop(1, i2) + pop(1, i3) + pop(1, i4) + pop(1,
i5)) / 5
Next i5: Next i4: Next i3: Next i2: Next i1
' sort in ascending order
For i = 1 To n - 1
k = i
For j = i + 1 To n
If x(j) < x(k) Then k = j
Next j
If k <> i Then
t = x(i): x(i) = x(k): x(k) = t
End If
Next i
Worksheets("Data").Range("A1:A2002").Value =
WorksheetFunction.Transpose(x)
End Sub
Function genprob(ref As Double, rng As Range) As Double
Dim pop As Variant
Dim x As Double
Dim i1 As Integer, i2 As Integer, i3 As Integer, i4 As Integer, i5 As
Integer
Dim cnt As Long, n As Long
If rng.Count <> 14 Then Exit Function
pop = rng.Value
If UBound(pop, 1) <> 1 Then Exit Function ' must be horizontal
n = 0: cnt = 0
For i1 = 1 To 10
For i2 = i1 + 1 To 11
For i3 = i2 + 1 To 12
For i4 = i3 + 1 To 13
For i5 = i4 + 1 To 14
n = n + 1
x = (pop(1, i1) + pop(1, i2) + pop(1, i3) + pop(1, i4) + pop(1,
i5)) / 5
If x <= ref Then cnt = cnt + 1
Next i5: Next i4: Next i3: Next i2: Next i1
genprob = cnt / n
End Function
Function minmax(mm As Integer, rng As Range) As Double
Dim pop As Variant
Dim x As Double
Dim i1 As Integer, i2 As Integer, i3 As Integer, i4 As Integer, i5 As
Integer
If rng.Count <> 14 Then Exit Function
pop = rng.Value
If UBound(pop, 1) <> 1 Then Exit Function ' must be horizontal
xmin = 1E+308
xmax = -1E+308
For i1 = 1 To 10
For i2 = i1 + 1 To 11
For i3 = i2 + 1 To 12
For i4 = i3 + 1 To 13
For i5 = i4 + 1 To 14
x = (pop(1, i1) + pop(1, i2) + pop(1, i3) + pop(1, i4) + pop(1,
i5)) / 5
If x < xmin Then xmin = x
If x > xmax Then xmax = x
Next i5: Next i4: Next i3: Next i2: Next i1
minmax = IIf(mm < 0, xmin, xmax)
End Function
)