I have this set of data:

0   700
1   350
2   250
3   300
4   150
5   145
6   150
7   147

being the first column about the type of the event (zero days, onde day, etc) and the second column its frequency.

How can I take this information and calculate the mean and standard deviation of a normal distribution that fits this data? (there is only positive values for the events tough)

Is there a way to do this in Excel?

  • 1
    $\begingroup$ A normal distribution would be a very poor description of this data; it is bounded at zero by definition and the distribution is highly skewed. A negative binomial might be more appropriate. But what are you trying to achieve more generally? $\endgroup$ – mkt - Reinstate Monica Jun 3 '18 at 8:29

In order to have a normal distribution, the variable of interest must take on values between $(-\infty,\infty)$. It doesn't look like that is true in your dataset. Your data looks like it takes on zero and positive integers. Consequently, without transforming the data your generated distributions will appear to be funky because the CLT fails. You can standardize your variables. Doing this will give you a smooth curve for the standardized variable.

Recall standardizing variables can be expressed by: $\frac{x_{i}-\mu}{\frac{\sigma}{\sqrt[]{n}}}$. $x_{i}$ is the individual variable of interest, $\mu$ is the mean, $n$ is the number of observations, and $\sigma$ is the standard deviation associated with the data.

As for the excel part of this question, you probably should ask that on StackOverflow. Any statistical programming language or Excel has the capabilities to help you with this problem.

  • $\begingroup$ Thanks for the tip! I will post it! Nonetheless, i tried the standardization of the variable for the real data and some values are positive and others negative... $\endgroup$ – chaosKnight May 24 '18 at 14:00
  • $\begingroup$ The initial assertion in the answer changes the meaning of "normal distribution" in a very subtle but important way. The fact is that a Normal model not only can work well for bounded variables, it frequently is used for just such purposes. (See Johnson & Kotz's books on distributions for many examples.) The fact that a Normal variable has unbounded support is important mathematically, but an appropriate approximation will assign such an astronomically low probability to values beyond the bounds that it can still be effective for statistical reasoning and analysis. $\endgroup$ – whuber Jun 22 '18 at 13:23

Well, you can find the mean and sd of the data in the usual way and you can then find a Normal distribution with that mean and sd. But it's a bad idea. The Normal won't be remotely close to your data. This is apparent without doing any calculations since

a) Your data are all non-negative integers whereas the Normal has a range from $ - \infty$ to $\infty$ and can take any value

b) The highest proportion is at one end whereas the Normal has a peak in the middle.

  • $\begingroup$ For a reaction to your point (a), please see my comment to the other answer at stats.stackexchange.com/questions/347817/…. To put it simply, for almost all applications the unboundedness of the Normal distribution simply is not relevant to assessing its appropriateness unless the Normal approximation assigns an appreciable probability to unrealistic numbers. $\endgroup$ – whuber Jun 22 '18 at 13:23

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.