I have to take issue with your formula to get from RAND() to a number between
1 and 240. It doesn't give every person an equal chance of being selected.
The "standard" formula for getting a random integer between 1 and N is
=INT(RAND()*N)+1
You want to generate a random number, which starts out in the range >=0, < 1,
and categorize it into one of 240 bins of *equal* size, so you multiply by
240. Assuming no problems with the RNG, RAND()*240 will give you a uniform
distribution between 0 and 239.99999999. No problem so far.
But you can't ROUND those numbers to get values between 1 and 240.
First, 0.5 will always round up. That's biased. It should round up half of the
time and down half of the time. (I expect that's more of a theoretical problem
than a real issue here.)
Second issue is the width of the bins: numbers >= 0.50 and < 1.5 will round to
1, >= 1.5 and < 2.5 will round to 2, etc. That's fine: for each of those bins
the width is 1.
But there are problems at the ends of the range:
Values >= 0 and < 0.5 will round to 0. The width of that bin is only half of
what it should be.
And at the other end, numbers between 239.5 and 240 will round to 240. Again,
this bin is only half-size.
So you have 239 bins of the correct size and 2 that are only half-size. IOW,
the distribution is not uniform.
This causes 2 problems:
(a) there's probably no "Person 0", so you'll get an error if RAND()*240 <
0.5; the chances of that are ~ 1/480, but they are not 0;
(b) the person at the end of the list, #240, has only half the chance of being
selected as the other persons; this is, IMO, significant.
I did the following to illustrate. I generated 65000 random integers between 1
and 10, so each number would occur with a frequency of about 6500.
In A1:A65000 I put the formula =RAND()*10
In B1, =ROUND(A1,0) and copy down
In C1, =INT(A1)+1 and copy down
I did histograms on columns B and C, with bins numbered 0 to 11. That's 12
bins, and the first and last *should* be empty.
Here's the histogram data:
Column B numbers Column C numbers
Bin Freq Bin Freq
0 3266 <<< 0 0 <<<
1 6597 1 6502
2 6632 2 6412
3 6338 3 6433
4 6518 4 6667
5 6375 5 6654
6 6495 6 6530
7 6414 7 6403
8 6621 8 6359
9 6573 9 6553
10 3171 <<< 10 6487
11 0 11 0 <<<
Note the column B formula distributes the numbers into 11 bins instead of 10:
the 9 middle bins have values between 6338 and 6632, the two at the ends are
roughly "half-size", with only 3266 and 3171 values, respectively.
OTOH, with the correct formula, in column C, there are only 10 bins containing
data, all of about the same size (range 6359-6667).
