Asymmetrical Normal distributions

[JenniferMurphy]

Does Excel have functions that will enable me to calculate Z scores for Normal distributions that are not symmetrical?

Suppose I have all of the data for all of the major league baseball teams going back over 100 years. Suppose I want to calculate the mean and std dev for win streaks of various lengths over the years. I would expect the tallies to be inversely proportional to the length of the streak. In any given year, there will be far more 3-game than 20-game win streaks. Since it is not possible to have negative tallies, these distributions are not symmetrical. The right tail is infinitely long, the left is finite.

Assuming these are Normal distributions, does Excel have functions that take this into account?

 

[Kaper]

Hi,

Honestly: My short answer - not symmetrical = not normal.

Best,

 

[JenniferMurphy]

Honestly: My short answer - not symmetrical = not normal.

Is this a serious answer or are you making a pun?

Do you know that there is no such thing as a normal distribution where one tail is not infinite, as in the baseball example I gave?

 

[Kaper]

Serious.
No pun, no quibble, not at all.

Normal distribution is just and only:

f(x|mi,sigma^2) = 1/sqrt(2 * pi * sigma^2) * exp ^ -((x-mi)^2)/(2*sigma^2)

and obviously it is always symmetrical against mi

(see for instance: http://www.statisticshowto.com/probability-and-statistics/normal-distributions/ )

You may refer for instance to log-normal distribution ( see http://www.statisticshowto.com/lognormal-distribution/ ), or other not-normal distributions.

Log-normal is assymetrical (one of several) and this particular could be the one you are interested in, but let me say again: there is nothing like "asymetric normal"

 

[JenniferMurphy]

What about "truncated" normal distributions -- also discussed on that same website?

http://www.statisticshowto.com/truncated-normal-distribution/

Are they not asymmetrical?

 

[Kaper]

Hi,
Truncated normal is not just normal (see chair vs. electric chair ).
Anyway, leaving this terminology discussion aside, and going back to merit (but still soft polemic here) I do not think such distribution (win streaks of various lengths) will be "in central part symmetrical", then just one tail left, and other cut):

As for possible approach - if you could fit truncated normal to your data, then you can calculate mean and standard deviation of this distribution. You probably noticed workbook by W. Joel Schneider http://www.ilstu.edu/~wjschne/138/AreaUnderCurve.xlsx on a page you linked above (if not download it) and you are ready to have Z scores calculated with basic equation:

Zx=(x-mean)/stddev

 

[JenniferMurphy]

OK. After more reading and thought, I have come to a couple of conclusions.

(1) My data probably does fit a normal distribution without truncation, but there is a small problem with certain values.

(2) An asymmetrical distribution, such as the log-normal is probably not necessary. In any case, I was not able to figure out how to apply it.

(3) I came up with a completely different approach that involves normalizing (mapping?) all of the values onto the range [-1, +1]. This has several advantages.

All of this is too much to post in here. So I created a short (5 page) write-up of the problem and my analysis. I can't attach anything here, so I uploaded it to this Dropbox folder.

https://www.dropbox.com/sh/eygt608ozcqjavh/AACNBdjDUCO_Ut-NiyFZVaOFa?dl=0

If anyone had the time to read it, I would appreciate any comments.

Cheers, JM

 

[Kaper]

Well,
This thread goes into direction of statistical forum rather than excel devoted one.

But as for normalization - if it is to be used with normal or even truncated normal (but truncated only from one tail side), normalization by range, based on max and min values from experiment(s), as [-1,1] doeas not convince me.
I'd rather go for normalization with avg = 0 and SD = 1. Then it could be represented as normal N(0,1) (in excel 2007 and earlier NORMSINV would be useful, in 2010+ it is still available, but NORM.S.INV is suggested).
If I find some more time (5 pages made me a bit reluctant - so may be during weekend), I'll have a look on your full description.

Best,

 

[JenniferMurphy]

Well,
This thread goes into direction of statistical forum rather than excel devoted one.

Yes, I know. That's what I said in my initial post. But I already posted it in a statistics forum and got next to nothing back. I always get better answers here than almost anywhere else.

But as for normalization - if it is to be used with normal or even truncated normal (but truncated only from one tail side), normalization by range, based on max and min values from experiment(s), as [-1,1] doeas not convince me.
I'd rather go for normalization with avg = 0 and SD = 1. Then it could be represented as normal N(0,1) (in excel 2007 and earlier NORMSINV would be useful, in 2010+ it is still available, but NORM.S.INV is suggested).

That was my initial inclination, as well, but having a fixed range has some benefits for me and I like that the exact matches get scores of zero, which the z scores don't.

If I find some more time (5 pages made me a bit reluctant - so may be during weekend), I'll have a look on your full description.

I just made several improvements in it and caught a few typos. Most of it is a quick read.

 

[AhoyNC]

Maybe these links will be of interest.

http://file.scirp.org/Html/8-7403543_74910.htm
http://tuvalu.santafe.edu/~redner/pubs/pdf/baseball.pdf