1

I am trying to simulate a number of different distribution types for a project using Excel. Right now, I have generated a normal distribution with a mean of 35 and a standard deviation of 3.33. So far so good.

I would like to also generate some other distribution types.

One I have tried is a lognormal. To get that, I am using the following code: 

=(LOGNORM.INV(RAND(),LN(45^2/SQRT(45^2+3.33^2)),SQRT(LN((45^2+3.33^2)/4.5^2))

It produces some output, but I would welcome anyone's input on the syntax.

What I really want to try to do is a power law distribution. From what I can tell, Excel does not have a built-in function to randomly generate this data. Does anyone know of a way to do it, besides switching software packages?

Thanks for any help you can provide.

E

JNevill
  • 46,980
  • 4
  • 38
  • 63
EHL
  • 43
  • 5
  • What do you mean by "power law distribution"? It doesn't seem to correspond to a well-defined distribution such as a beta distribution but instead seems to be a family of distributions. In other words -- there are different *kinds* of power law distributions. Which kind are you asking about? – John Coleman May 03 '17 at 16:59
  • Hi John, Thanks for your reply. I would say a Pareto distribution (type 1) would probably be the best fit for what I am trying to generate. What I am basically trying to do is capture the effect of a long tail, so, in that sense, any member of the power law family would probably work. Although I would ideally want a probably distribution function and not a cumulative distribution function. – EHL May 04 '17 at 17:34

1 Answers1

2

For the (type I) Pareto distribution, if the parameters are a min value xm and an exponent alpha then the cdf is given by

p = 1 - (xm/x)^alpha

This gives the probability, p, that the random variable takes on a value which is <= x. This is easy to invert, so you can use inverse sampling to generate random variables which follow that distribution:

x = xm/(1-p)^(1/alpha) = xm*(1-p)^(-1/alpha)

If p is uniform over [0,1] then so is 1-p, so in the above you can just use RAND() to simulate 1/p. Thus, in Excel if you wanted to e.g. simulate a type-1 Pareto distribution with xm = 2 and alpha = 3, you would use the formula:

= 2 * RAND()^(-1/3)

If you are going to be doing this sort of thing a lot with different distributions, you might want to consider using R, which can be called directly from Excel using the REXcel add-in. R has a very large number of built-in distributions that it can directly sample from (and it also uses a better underlying random number generator than Excel does).

John Coleman
  • 51,337
  • 7
  • 54
  • 119
  • John, Thanks ever so much for your comment. This is extraordinarily helpful. I have never tried R (been meaning to learn for a while now). Thanks again. I really appreciate your taking the time. I will try this out today. – EHL May 05 '17 at 14:55
  • Glad to help. If the answer works for you, you could always make it as "accepted". – John Coleman May 05 '17 at 14:57
  • I have been playing around with the construction of type 1 power laws that you recommended. They form the correct shape and all seems well. I noticed, however, that with the structure of the equation for the simulated data means that a smaller exponent (alpha) results in a larger or more pronounced distribution. Mathematically, I understand why, since -1/0.5 is larger, for example, than -1/3, but it is a bit counter intuitive to think that a smaller alpha results in a more pronounced distribution. Do you happen to know of a good resource to explain why this is the case? Thanks! – EHL May 08 '17 at 19:43
  • I don't really have a lot of familiarity with the distribution so I lack intuition about the meaning of the exponent. Perhaps this can shed light on it: https://en.wikipedia.org/wiki/Pareto_index (even though this is specifically about economic applications it might help with non-economic interpretations as well). – John Coleman May 08 '17 at 19:48
  • Thanks, John. I will take a look and see if I can't suss out the meaning here. When I do, I will let you know. – EHL May 10 '17 at 14:10