Wednesday, January 16, 2013

sampling distribution in excel

This Microsoft Excel file ( Download xlsx file ) is my attempt to show the sampling distribution of p-hat for different sample sizes.  The sample size can be changed using a slider/scroll type button.  I produced this on a MacBook Pro, and I'm not sure how well it transfers to other versions of excel. 

The construction was moderately simple and mostly correct.  I am pretty sure that a graph in Excel cannot have its input range defined to be variable, and that provided the bulk of my difficulties.  In the sampling distribution, a sample size of 10 would make us want to see a histogram with 11 bars, while a sample size of n = 100 would require 101 bars, though of course many would be very small.  If I made a graph that could accommodate both, the variation on the x-axis would distract from the primary objective, which is to show that the sampling distribution becomes smoother, less skewed, and in fact more normal as n increases.

The idea in this file is to work with a large number of rows that are grouped according to n.  The real bars are very narrow and the spacing between bars is zero.  Then we can group the bars according to the value of n,  so that a group of 100 bars may be told to act like 4 big bars or 5 or 7.  In this last case of course, the bars are not truly of equal width (since 7 does not divide 100) but I believe they are close enough in appearance that the picture effectively approximates the sampling distribution. 

No comments:

Post a Comment