在Excel中生成两个值之间的随机整数的正态分布列表 [英] Generating a normally distributed list of random integers between two values in Excel

问题描述

我需要使用给定的平均值生成正态分布的下限和上限之间的随机整数值的列表。例如：1到10之间的150个随机值的铃声分布，平均值为3.平均值可以近似（例如2.8和3.2之间的浮点数），但是列表的数字需要是整数。

此线程似乎指向可能的方向。使用NORM.INV（RAND（），mean，dev）我可以生成浮点数，然后将它们转换为整数，但是我不知道如何限制最小值和最大值。 RANDBETWEEN（）可以解决这个问题，但它似乎并不适用于NORM.INV

解决方案

正态分布是一个连续的分布无限范围，所以你无法完成你所说的目标。您可以使用四舍五入/天花板/地板离散化，但是您也必须拒绝超出所需范围的任何结果，并重复尝试 - 不是Excel中最简单的事情，离散化和截断将会改变您的平均值。

你可以从一个离散的分布中生成一个有限的行为，收敛到正常。两个这样的分布是泊松和二项式。因为你想要一个固定的范围，我建议二项式。

二项描述从 n 独立审判，其中每个都有屈服成功的概率 p 。二项式的范围为0，...， n ，其预期值为 n * p 。如果要实际的范围为1，...，10，平均值为3，则应该生成一个二项式，其中 n = 9 ，平均值为2，然后移位它通过添加1.要获得平均值2，样本大小为9，您需要 p = 2/9 。

$ b $总而言之，生成 x =二项式（n = 9，p = 2/9）+ 1 的150个实例。分析Tookpak或加载项（如Crystal Ball或@RISK）可以为您生成二项式，或者只需将9个单元格（如果 RANDOM< = 2/9 ，否则为0。

I need to generate a list of random integer values between a lower and an upper limit for a normal distribution using a given mean. For example: a bell distribution of 150 random values between 1 and 10, with a mean of 3. The mean can be approximated (e.g. a float between 2.8 and 3.2) but the numbers of the list need to be integers.

This thread seems to point to possible directions. With NORM.INV(RAND(),mean,dev) I can generate floats and then transform them to integers, but I don't know how to limit the minimum and maximum values. RANDBETWEEN() could solve this but it does not seem to work with NORM.INV

解决方案

The normal distribution is a continuous distribution with an infinite range, so you can't accomplish your stated goal. You can discretize it with rounding/ceiling/floor, but then you also have to reject any outcomes that are outside your desired range and repeat the attempt -- not the easiest thing to in Excel, and both the discretization and truncation will shift your mean.

What you can do is generate from a discrete distribution which has limit behaviors that converge to a normal. Two such distributions are the Poisson and the binomial. Since you want a fixed range, I'd suggest the binomial.

A binomial describes how many "successes" you will get from n independent trials, each of which has probability p of yielding "success." The range of a binomial is 0,...,n, and its expected value is n*p. If you want an actual range of 1,...,10 and a mean of 3, you should generate a binomial with n = 9 and a mean of 2, then shift it by adding 1. To get a mean of 2 with a sample size of 9, you need p = 2/9.

In summary, generate 150 instances of x = binomial(n = 9, p = 2/9) + 1. Analysis Tookpak or add-ins such as Crystal Ball or @RISK can generate binomials for you, or you can just sum 9 cells which contain 1 if RANDOM <= 2/9, 0 otherwise.

这篇关于在Excel中生成两个值之间的随机整数的正态分布列表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！