Ei liene normaalijakautunut, jos ymmärrän tämän kohdan oikein.
Returns an evenly distributed random number
How to Calculate a Random Number from a Normal Distribution
Remember that the NORMINV function returns a value given a probability:
NORMINV(probability, mean, standard_dev)
Also, remember that RAND() function returns a random number between 0 and 1. That is, RAND() generates random probabilities. Therefore, it seems logical that you could use the NORMINV function to calculate a random number from a normal distribution, using this formula:
=NORMINV(RAND(), mean, standard_dev)
However, Jerry W. Lewis -- a former Excel MVP and a professional statistician -- offers a stern comment about this approach. "Using NORMINV(RAND(),...) to generate Normal variates is totally unacceptable prior to Excel XP, and is only marginal in XP. This is because of inadequacies in NORMINV and in the tails of NORMDIST itself."
"NORMINV prior to Excel XP produced a very un-normal fraction of values around 6 million standard deviations from the mean," Jerry wrote. "This is due to inaccuracies in the implementation of NORMDIST and NORMINV. Excel XP brought those values into a less obviously wrong location, but otherwise did little to improve the situation. NORMINV in Excel 2003 is a decent implementation."
Instead, Jerry recommends the Box-Muller method described here: http://mathworld.wolfram.com/Box-Muller ... ation.html
This method, he wrote, is limited only by the inadequacies of the RAND() function prior to Excel 2003, which had unacceptable autocorrelation.
The Box-Muller approach suggests that Excel users should use this formula to calculate a random number from a normal distribution:=SQRT(-2*LN(RAND()))*SIN(2*PI()*RAND())
The Box-Muller method is mathematically exact, Jerry writes, if implemented with a perfect uniform random number generator and infinite precision.
Jos tuo ei kelpaa, niin pitää varmaankin pelleillä makrojen/VBA:n ja tuon Random Number Generationin kanssa.