How to fit a truncated normal distribution to existing data?

[JenniferMurphy]

I have some data that I have been collecting from a game of solitaire. When I plot it opn a scatter chart, it looks like a normal distribution -- actually a truncated normal distribution. I have been able to more or less fit a standard normal distribution to the data after some trial and error (mostly error), but my attempts at fitting a truncarted normal distribution have failed.

xl2bb tells me that the data is too large to post here, so I have uploaded the workbook to this OneDrive folder.

Solitaire Data

The workbook has 2 sheets. One has my data and the equations I have tried to make fit. The other has detailed explanations. There is also a csv file with just the data.

Here's one of the charts:

These standard normal distributions almost fit, but I think a truncated normal would fit better, because the data is actually truncated on the left. This is all explained in the Overview sheet.

 

[JenniferMurphy]

I understand that some may be reluctant to download a workbook from an unknown source, so I'll try to provide more information here.

The complete dataset is too large for xl2bb, but here are the first 20+ rows. (The colors match the plots below)

Solitaire Data.xlsx
	B	C	D	E	F	G
3	Mean	85.36853	85.36853	85.36853	82.00000	85.36853
4	Std Dev	7.5333	7.5333	7.5333	5.0000	7.53330
5	MaxEq	n/a	0.05296	0.05296	0.07979
6	MaxTbl	41	0.05289	40.9510	41.00000	1.55421E-09
7	MaxTbNdx	83	85	85	82
8	Moves	Wins	Norm Dist	Scaled ND	Adj SD	Trunc ND
9	52	0	0.00000	0.00225	0.00000	6.89187E-30
10	53	0	0.00001	0.00402	0.00000	6.89187E-30
11	54	0	0.00001	0.00704	0.00001	6.89187E-30
12	55	0	0.00002	0.01213	0.00002	6.89187E-30
13	56	0	0.00003	0.02054	0.00006	6.89187E-30
14	57	0	0.00004	0.03415	0.00015	6.89187E-30
15	58	0	0.00007	0.05581	0.00041	6.89187E-30
16	59	0	0.00012	0.08960	0.00104	6.89187E-30
17	60	0	0.00018	0.14135	0.00256	6.89187E-30
18	61	0	0.00028	0.21908	0.00606	6.89187E-30
19	62	0	0.00043	0.33363	0.01375	6.89187E-30
20	63	1	0.00064	0.49919	0.03000	3.07475E-29
21	64	0	0.00095	0.73387	0.06289	6.89187E-30
22	65	0	0.00137	1.06004	0.12664	6.89187E-30
23	66	0	0.00194	1.50442	0.24502	6.89187E-30
24	67	0	0.00271	2.09780	0.45547	6.89187E-30
25	68	0	0.00371	2.87412	0.81348	6.89187E-30
26	69	3	0.00500	3.86897	1.39595	5.80493E-28
27	70	7	0.00661	5.11720	2.30153	1.67471E-25
28	71	9	0.00859	6.64992	3.64579	2.55914E-24
29	72	9	0.01097	8.49079	5.54875	2.55914E-24
30	73	15	0.01376	10.65190	8.11385	5.98266E-21
Sheet3

Cell Formulas
Range	Formula
C3	C3	=SUMPRODUCT(Moves,Wins)/SUM(Wins)
D3:E3,G3	D3	=WinsMean
C4	C4	=STDEV(Wins)
D4:E4,G4	D4	=WinsStdDev
D5:F5	D5	=1/StdDevs/SQRT(2*PI())
C6	C6	=MAX(Wins)
D6	D6	=MAX(NormDist)
E6	E6	=MAX(ScaledND)
F6	F6	=MAX(AdjustedSD)
G6	G6	=MAX(TruncND)
C7	C7	=INDEX(Moves,XMATCH(@MaxTbls,Wins))
D7	D7	=INDEX(Moves,XMATCH(@MaxTbls,NormDist))
E7	E7	=INDEX(Moves,XMATCH(@MaxTbls,ScaledND))
F7	F7	=INDEX(Moves,XMATCH(@MaxTbls,AdjustedSD))
D9:D30	D9	=NORM.DIST(Moves,Means,StdDevs,FALSE)
E9:F30	E9	=NORM.DIST(Moves,Means,StdDevs,FALSE)/MaxEqs*WinsMaxTbl
G9:G10	G9	=NORM.DIST(Wins,Means,StdDevs,FALSE)/(1-NORM.DIST(52,Means,StdDevs,TRUE))
G11:G30	G11	=NORM.DIST(Wins,Means,StdDevs,FALSE)/(NORM.DIST(192,Means,StdDevs,TRUE)-NORM.DIST(52,Means,StdDevs,TRUE))

Named Ranges
Name	Refers To	Cells
AdjustedSD	=OFFSET(Sheet3!AdjustedSDHdr,1,0):OFFSET(Sheet3!AdjustedSDFtr,-1,0)	F6:F7
AdjustedSDFtr	=Sheet3!$F$150	F6:F7
AdjustedSDHdr	=Sheet3!$F$8	F6:F7
AdjustedSDSD	=Sheet3!$F$4	F5, F9:F30
MaxTblND	=Sheet3!$D$6	C7:F7
MaxTbls	=Sheet3!$6:$6	C7:F7
MaxTblScND	=Sheet3!$E$6	C7:F7
Moves	=OFFSET(Sheet3!MovesHdr,1,0):OFFSET(Sheet3!MovesFtr,-1,0)	C7:F7, D9:F30, C3
MovesFtr	=Sheet3!$B$150	C7:F7, D9:F30, C3
MovesHdr	=Sheet3!$B$8	C7:F7, D9:F30, C3
NormDist	=OFFSET(Sheet3!NormDistHdr,1,0):OFFSET(Sheet3!NormDistFtr,-1,0)	D6:D7
NormDistFtr	=Sheet3!$D$150	D6:D7
NormDistHdr	=Sheet3!$D$8	D6:D7
ScaledND	=OFFSET(Sheet3!ScaledNDHdr,1,0):OFFSET(Sheet3!ScaledNDFtr,-1,0)	E6:E7
ScaledNDFtr	=Sheet3!$E$150	E6:E7
ScaledNDHdr	=Sheet3!$E$8	E6:E7
TruncND	=OFFSET(Sheet3!TruncNDHdr,1,0):OFFSET(Sheet3!TruncNDFtr,-1,0)	G6
TruncNDFtr	=Sheet3!$G$150	G6
TruncNDHdr	=Sheet3!$G$8	G6
Wins	=OFFSET(Sheet3!WinsHdr,1,0):OFFSET(Sheet3!WinsFtr,-1,0)	G9:G30, C3:C4, C6:C7
WinsFtr	=Sheet3!$C$150	G9:G30, C3:C4, C6:C7
WinsHdr	=Sheet3!$C$8	G9:G30, C3:C4, C6:C7
WinsMaxTbl	=Sheet3!$C$6	C7:F7, E9:F30
WinsMean	=Sheet3!$C$3	G3, D3:E3
WinsStdDev	=Sheet3!$C$4	G4, D4:E4

My data is half truncated on the left only [52, ∞). The actual data is on [52,192]. Here’s the scatter plot:

I calculated the mean and std dev as:

Mean = 85.36853 (=SUMPRODUCT(Moves,Wins)/SUM(Wins))
Std Dev = 7.5333 (=STDEV(Wins))

Using those parameters, I plotted a normal distribution using this formula:

=NORM.DIST(@Moves,@Means,@StdDevs,FALSE)

Here’s that plot:

This appears to have the basic shape of my data, but on a smaller scale, so I scaled it up by dividing by the maximum value here (0.05296) and multiplying by the maximum of my data (41).

=NORM.DIST(@Moves,@Means,@StdDevs,FALSE)/@MaxEqs*WinsMaxTbl

Here’s that plot superimposed on the plot of my data:

It looks roughly like a fit, but shifted to the right and too wide. I fiddled around with the mean and std dev and came up with this:

This looks pretty good, but a truncated normal should be better, but this is where I am stuck.

Since my data is only truncated on the left, I tried this formula:

=NORM.DIST(@Wins,@Means,@StdDevs,FALSE)/(1-NORM.DIST(52,@Means,@StdDevs,TRUE))

I get values from 6.89187E-30 to 1.55421E-09.

I then tried this one:

=NORM.DIST(@Wins,@Means,@StdDevs,FALSE)/(NORM.DIST(192,@Means,@StdDevs,TRUE)-NORM.DIST(52,@Means,@StdDevs,TRUE))

That gets the same values.

Can anyone tell me what I am doing wrong?

 

[KRice]

Jennifer,
I'm sorry for the delay...I got sidetracked. I had intended to follow up with you about this. When I first saw your data, I assumed that if a normal distribution was reasonably representative of the data, it would be a truncated normal distribution because the lower left tail is cut off by very real constraints (i.e., you need a minimum number of moves to complete a game, so a win cannot occur below that threshold). I normally explore these types of data sets by scaling the area under a histogram of the data to 1...so other standard distributions whose cumulative distribution function has values on the interval [0,1] can be compared to the data. When this is done with your data set, the distribution of moves is very peaked...indicating a high kurtosis...and this suggests that a normal distribution may not be an appropriate descriptor for the data. One thing to be aware of: you can scale the histogram of actual data so that the peak is about the same as a standard normal curve (I believe that is what you did), and when you do this, the curves appear to be somewhat similar, but you can tell that the area under the curves (red vs. blue curves in the last post) are quite different.

I noticed in a recent post:

Is there a good statistical package that is not too expensive?

Can anyone recommend a good statistical package that is not too expensive or too difficult to master? My immediate need is the ability to test some experimental data to see if it is normally distributed -- that is if it follows (fits) a normal distribution curve. And if it does, what the mean...

www.mrexcel.com

...that you are exploring distributions and tests of normality.

For the particular data set here, I followed a methodology described in this tutorial:

I created a workbook based on your data and the Tukey Lambda method shown in the video. I will need to review this file further to refresh my memory about some details, but wanted to share it with you so that you can explore it too.

Dropbox

www.dropbox.com

On one worksheet, you will see the Lambda calculated suggests that a normal distribution is not appropriate. On another worksheet or two I investigated how to reconstitute your original raw data (a single column list indicating the number of moves) rather than the summary data presented (a two-column list indicating moves and # of wins). The reason is that built-in functions can be used directly on the raw data to compute skew and kurtosis that might offer hope for early screening to determine whether to consider a normal distribution further.

 

[JenniferMurphy]

Jennifer,
I'm sorry for the delay...I got sidetracked.

. . .

For the particular data set here, I followed a methodology described in this tutorial:

I created a workbook based on your data and the Tukey Lambda method shown in the video. I will need to review this file further to refresh my memory about some details, but wanted to share it with you so that you can explore it too.

Dropbox

www.dropbox.com

On one worksheet, you will see the Lambda calculated suggests that a normal distribution is not appropriate. On another worksheet or two I investigated how to reconstitute your original raw data (a single column list indicating the number of moves) rather than the summary data presented (a two-column list indicating moves and # of wins). The reason is that built-in functions can be used directly on the raw data to compute skew and kurtosis that might offer hope for early screening to determine whether to consider a normal distribution further.

Thanks for the reference and the workbook. I fear that some of it may be over my head, but I'll take a look. I really appreciate the edxtra time you took on this. I'm going to mark this as a solution. If I run into problems trying to wrap my head around the details, I'll repost.