Skew Normal Distribution

[nicedanmonkey]

I am trying to turn a formula for a skew normal distribution into an actual excel formula but am having trouble. I'm not sure how to implement integrals in excel. Here is a link to the formula and to the wikipedia article.

Formula: http://upload.wikimedia.org/math/6/2/8/628a96d51ba225eee1157849e8c52c9b.png
Wiki Article: http://en.wikipedia.org/wiki/Skew_normal_distribution

Basically I am going to have a series of X values in column A. In column B I will have the Skew Normal Distribution formula to give me a value for each X value.

I completed a similar spreadsheet with a Normal Distribution Formula

Formula: http://upload.wikimedia.org/math/e/9/9/e995ab18aed54262088171e9e8fc0d8b.png
Wiki Article: http://en.wikipedia.org/wiki/Normal_distribution

This is the excel formula I used
=((1/(SQRT(2*PI()*$I$1))*EXP(-((A2-$I$2)^2)/2*$I$1)))
I1 is the Variance and I2 is the Mean. A2 is the X value.

 

[shg]

Maybe like this:

       -A-- ---B----
   1      w        1
   2      e        0
   3      a        4
   4                
   5    x     pdf   
   6   -1.0 0.000015
   7   -0.8 0.000398
   8   -0.6 0.005463
   9   -0.4 0.040362
  10   -0.2 0.165689
  11    0.0 0.398942
  12    0.2 0.616396
  13    0.4 0.696178
  14    0.6 0.660986
  15    0.8 0.578985
  16    1.0 0.483926
  17    1.2 0.388372
  18    1.4 0.299455
  19    1.6 0.221842
  20    1.8 0.157900
  21    2.0 0.107982
  22    2.2 0.070949
  23    2.4 0.044789
  24    2.6 0.027166
  25    2.8 0.015831
  26    3.0 0.008864

The formula in B6 and down is

=2/w * NORMDIST((x-e)/w, 0, 1, FALSE) * NORMSDIST(a*(x-e)/w)

 

[nicedanmonkey]

That seems to work very well! Thank you for your help.

 

[shg]

You're welcome, glad it worked for you.

 

[jdb92064]

This worked for me too. Thanks.

Related question: For a skew-normal distribution with parameters (w,e,a) what is the location of the + and - sigma points? Or, if easier, what is the location of the Stanines?

Thanks.
--dave

 

[Threazy]

I, too, am interested in a skew-normal dist for Excel and am a newbie: what are the parameters w, e, and a?

 

[shg]

They are the scale, location, and shape parameters described in the linked wikipedia article.

Prof Adelchi Azzalini has a ton of information at http://azzalini.stat.unipd.it/SN/

 

[DaveM123456789]

Hi there,

I need to fit a skew normal distribution to the following data, I've looked all around trying to figure out how to calculate the scale, shape, and location, but can't figure it out.

Could you please explain how you'd go about making that calculation? It looks like I can't attach my data, but I'll paste it in a reply if it helps. Just let me know.

Thanks!

 

[shg]

Welcome to the board.

How much data?

Why do you think it's skew normal?

 

[DaveM123456789]

It's about 350 data points. It fits a normal distribution curve pretty well, but has a small portion of the tail (about 2%) that goes negative. The measurement is the number of days in advance a job books. So, can't go negative! Im away from my computer but off the top of my head the skew was about .5 and kurtosis was about .5 over normal (using excel's KURT function). I can past the data into a repaonse when I get home if that helps!

If it ends up fitting another distribution better, great! It just can't go negative. I tried to test it with a log normal curve and that came out nowhere near the data. Of course, I'm a decade removed from my my college stats classes, so I could be butchering something.

Thanks so much for your prompt reply!