Adding Random Numbers

[Gary''s Student]

Perfect!!
--
Gary''s Student - gsnu200792

The Central Limit Theory says that the sum of independent random
variables (all with the same distribution) is a normal distribution.
[....]
the following is a convenient way to generate one sum of 23 random
values between 0 and 1:

=norminv(rand(), 23/2, sqrt(23/12))

In that formula, RAND() is used simply to generate a random
probability for the NORMINV() function. The theory behind the formula
is as follows....

According to the CLT, the sum is a normal distribution regardless of
the distribution of the individual random variables being summed. The
distribution of the sum has a mean of N*m and a std dev of sqrt(N)*s,
where N is the number of random variables summed, and "m" and "s" are
the mean and std dev of each of the N random variables.

The OP indicated that the random variables are RAND(). RAND() should
have a uniform distribution (U) between 0 and 1. The mean of U(a,b)
is (b+a)/2, and the std dev is (b-a)/sqrt(12). For RAND(), these
simplify to 1/2 and 1/sqrt(12).

So the sum of 23 RAND() calls has a mean of 23*(1/2) and a std dev of
sqrt(23)*(1/sqrt(12)), which simplify to 23/2 and sqrt(23/12).

 

[Harlan Grove]

....
According to the CLT, the sum is a normal distribution regardless of
the distribution of the individual random variables being summed. The
distribution of the sum has a mean of N*m and a std dev of sqrt(N)*s,
where N is the number of random variables summed, and "m" and "s" are
the mean and std dev of each of the N random variables.

....

Not quite. The sum of iid random variables is ASYMPTOTICALLY normal.
Meaning, the distribution of the sum of N iid random variables becomes
normal as N approaches infinity. For smallish values of N, the normal
distribution is a rough approximation of the actual distribution of
the sum.

Also, the normal distribution with mean 23/2 and standard deviation
23/2 has a finite probability of NEGATIVE values and values GREATER
THAN 23. If RAND() returns a number less than 0.00034, your formula
will return a negative number, and if RAND() returns a number greater
than 0.99966, your formula will return a number greater than 23. Up to
the OP to decide whether this is OK or not.

 

[joeu2004]

Both good points. I agree with you entirely.

For smallish values of N, the normal distribution is a rough
approximation of the actual distribution of the sum.

Yes. I thought my sampling of 1000 was sufficiently demonstrative for
practical purposes. But you are correct to point out the risks.

=norminv(rand(), 23/2, sqrt(23/12))

[....]
Also, the normal distribution with mean 23/2 and standard deviation
23/2 has a finite probability of NEGATIVE values and values GREATER
THAN 23. If RAND() returns a number less than 0.00034, your formula
will return a negative number, and if RAND() returns a number greater
than 0.99966, your formula will return a number greater than 23.

Yes. For practical purposes, I should have written:

=max(0, min(23, norminv(rand(), 23/2, sqrt(23/12)) ))

That should cover the nearly "0.07%" probability that NORMINV() will
return values outside the expected limits, although arguably it alters
the normal distribution somewhat ;-).

Note: I am saying "0.07%" based on your assertions above -- that is,
the limits 0,00034 and 0.99966. In my experiments with Excel 2003,
the NORMINV() does not return negative until RAND() is something less
than 1E-16 , and NORMINV() did not exceed 23 even for 1 - 1E-16.

Thanks for posting those errata to my comments.


----- original posting -----