Asymmetrical Normal distributions

JenniferMurphy

JenniferMurphy

Well-known Member
Joined
Jul 23, 2011
Messages
2,687
Office Version
  1. 365
Platform
  1. Windows
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?
 
Sort by date Sort by votes

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.
You're welcome, hope they help. At first though it looks like your data would fit a Poisson distribution:
-asymmetrical.
-Left boarder at 0
-right boarder to infinite
-right skew
But after looking at the links above this probably wouldn't be the best fit of your data.
Good luck.
 
Upvote 0
AhoyNC said:
You're welcome, hope they help. At first though it looks like your data would fit a Poisson distribution:
-asymmetrical.
-Left boarder at 0
-right boarder to infinite
-right skew
But after looking at the links above this probably wouldn't be the best fit of your data.
Good luck.
Click to expand...

I think I have been confusing myself. Here's the raw data.

[TABLE="class: grid, width: 450"]
<tbody>[TR]
[TD="align: center"]R/C
[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]E[/TD]
[TD="align: center"]F[/TD]
[TD="align: center"]G[/TD]
[TD="align: center"]H[/TD]
[TD="align: center"]I[/TD]
[TD="align: center"]J[/TD]
[TD="align: center"]K[/TD]
[TD="align: center"]L[/TD]
[TD="align: center"]M[/TD]
[TD="align: center"]N
[/TD]
[/TR]
[TR]
[TD="align: center"]3
[/TD]
[TD]NumWins
[/TD]
[TD="align: center"]0
[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]3
[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]6[/TD]
[TD="align: center"]7[/TD]
[TD="align: center"]8[/TD]
[TD="align: center"]9[/TD]
[TD="align: center"]10
[/TD]
[/TR]
[TR]
[TD="align: center"]4
[/TD]
[TD]ActNum[/TD]
[TD="align: center"]16
[/TD]
[TD="align: center"]12 [/TD]
[TD="align: center"]8 [/TD]
[TD="align: center"]7 [/TD]
[TD="align: center"]6 [/TD]
[TD="align: center"]0 [/TD]
[TD="align: center"]2 [/TD]
[TD="align: center"]2 [/TD]
[TD="align: center"]0 [/TD]
[TD="align: center"]1 [/TD]
[TD="align: center"]0
[/TD]
[/TR]
</tbody>[/TABLE]

Row 3 shows the number of wins in each streak. Row 4 shows how many times each length streak occurred. This team had 16 0-win streaks (back-to-back losses), 12 1-win streaks, 8 2-win streaks, etc.

The overall stats for this team are:

[TABLE="class: grid, width: 158"]
<tbody>[TR]
[TD]Wins[/TD]
[TD="align: center"]108[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Losses[/TD]
[TD="align: center"]54[/TD]
[TD](Streaks)[/TD]
[/TR]
[TR]
[TD]Games[/TD]
[TD="align: center"]162[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]PWin[/TD]
[TD="align: center"]66.67%[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

My goal is to calculate a relative rating for each streak tally. I'd like to know which streak was the most over or under the expected tally, the next most, and so on.

Here are some of the calculations I have done. The row and column numbers are not consecutive because some results have been hidden for simplicity. Column Q shows the formulas used for the calculations.

[TABLE="class: grid, width: 750"]
<tbody>[TR]
[TD="align: center"]R/C
[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]E[/TD]
[TD="align: center"]F[/TD]
[TD="align: center"]G[/TD]
[TD="align: center"]H[/TD]
[TD="align: center"]I[/TD]
[TD="align: center"]J[/TD]
[TD="align: center"]K[/TD]
[TD="align: center"]L[/TD]
[TD="align: center"]M[/TD]
[TD="align: center"]N[/TD]
[TD="align: center"]Q[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD]NumWins
[/TD]
[TD="align: center"]0
[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]6[/TD]
[TD="align: center"]7[/TD]
[TD="align: center"]8[/TD]
[TD="align: center"]9[/TD]
[TD="align: center"]10[/TD]
[TD="align: center"]Formulae [/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD]ActNum[/TD]
[TD="align: center"]16
[/TD]
[TD="align: center"]12 [/TD]
[TD="align: center"]8 [/TD]
[TD="align: center"]7 [/TD]
[TD="align: center"]6 [/TD]
[TD="align: center"]0 [/TD]
[TD="align: center"]2 [/TD]
[TD="align: center"]2 [/TD]
[TD="align: center"]0 [/TD]
[TD="align: center"]1 [/TD]
[TD="align: center"]0
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD]ExpNum[/TD]
[TD="align: center"]18.00
[/TD]
[TD="align: center"]12.00[/TD]
[TD="align: center"]8.00[/TD]
[TD="align: center"]5.33[/TD]
[TD="align: center"]3.56
[/TD]
[TD="align: center"]2.37[/TD]
[TD="align: center"]1.58[/TD]
[TD="align: center"]1.05[/TD]
[TD="align: center"]0.70[/TD]
[TD="align: center"]0.47[/TD]
[TD="align: center"]0.31[/TD]
[TD]=ExpPC*Streaks[/TD]
[/TR]
[TR]
[TD="align: center"]6[/TD]
[TD]NumErr[/TD]
[TD="align: center"]-2.00[/TD]
[TD="align: center"]=0.00[/TD]
[TD="align: center"]=0.00[/TD]
[TD="align: center"]+1.67[/TD]
[TD="align: center"]+2.44[/TD]
[TD="align: center"]-2.37[/TD]
[TD="align: center"]+0.42[/TD]
[TD="align: center"]+0.95[/TD]
[TD="align: center"]-0.70[/TD]
[TD="align: center"]+0.53[/TD]
[TD="align: center"]-0.31[/TD]
[TD]=ActNum-ExpNum[/TD]
[/TR]
[TR]
[TD="align: center"]13[/TD]
[TD]ActPC[/TD]
[TD="align: center"]29.63%[/TD]
[TD="align: center"]22.22%
[/TD]
[TD="align: center"]14.81%[/TD]
[TD="align: center"]12.96%[/TD]
[TD="align: center"]11.11%[/TD]
[TD="align: center"]0.00%[/TD]
[TD="align: center"]3.70%[/TD]
[TD="align: center"]3.70%[/TD]
[TD="align: center"]0.00%[/TD]
[TD="align: center"]1.85%[/TD]
[TD="align: center"]0.00%[/TD]
[TD]=ActNum/Streaks[/TD]
[/TR]
[TR]
[TD="align: center"]14[/TD]
[TD]ExpPC[/TD]
[TD="align: center"]33.33%[/TD]
[TD="align: center"]22.22%[/TD]
[TD="align: center"]14.81%[/TD]
[TD="align: center"]9.88%[/TD]
[TD="align: center"]6.58%[/TD]
[TD="align: center"]4.39%[/TD]
[TD="align: center"]2.93%[/TD]
[TD="align: center"]1.95%[/TD]
[TD="align: center"]1.30%[/TD]
[TD="align: center"]0.87%[/TD]
[TD="align: center"]0.58%[/TD]
[TD]=(PWin^NumWins)*(1-PWin)[/TD]
[/TR]
[TR]
[TD="align: center"]15[/TD]
[TD]PCErr[/TD]
[TD="align: center"]-3.70%[/TD]
[TD="align: center"]=0.00%[/TD]
[TD="align: center"]=0.00%[/TD]
[TD="align: center"]+3.09%[/TD]
[TD="align: center"]+4.53%[/TD]
[TD="align: center"]-4.39%[/TD]
[TD="align: center"]+0.78%[/TD]
[TD="align: center"]+1.75%[/TD]
[TD="align: center"]-1.30%[/TD]
[TD="align: center"]+0.98%[/TD]
[TD="align: center"]-0.58%[/TD]
[TD]=ActPC-ExpPC[/TD]
[/TR]
</tbody>[/TABLE]

Row 5 shows the expected tallies. It depends on the data in row 14. Row 6 shows the error in the tallies. The tallies themselves follow a geometric decay curve. (See formula in Q14.) But I was hoping that the errors might fit a normal distribution. When I originally posted this question, I asked about an asymmetric normal distribution. I think I was confusing myself between the tallies and the tally errors. The tallies have a lower limit of zero, but the tally errors should have a mean close to zero.

And similarly for rows 13-15. Row 13 shows the actual % of the tallies. Row 14 shows the expected %s. Row 15 shows the difference in these %s (the error). I thought that might also fit a normal distribution.

Anyway,I agree with you that none of these fit a Poisson.
 
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Question Question
Spread cost with skewed normal distribution
Replies
0
Views
2K
JontyO
J
JenniferMurphy
  • Question Question
Comparing tallies of different length win streaks (statistics question)
Replies
1
Views
732
JenniferMurphy
JenniferMurphy
T
  • Question Question
Winning Streak Formula Solution..Please !!!
2
Replies
12
Views
3K
Andrew Fergus
Andrew Fergus
B
  • Question Question
VBA READ/WRITE .MP3 & .WMA PROPERTIES (SOLVED)
2 3
Replies
21
Views
59K
erik.van.geit
erik.van.geit

Forum statistics

Threads
1,223,911
Messages
6,175,327
Members
452,635
Latest member
laura12345

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top