To get a bin width, divide the range (156) by the number of bins (9) which results in 17.33, round this up to an even 20 to produce nice round bin widths. Using Sturges’ formula the number of bins is 9, using the square root method the number of bins is 15. Start by calculating the minimum (28) and maximum (184) and then the range (156). This is done by creating bins of a certain width and counting the frequency of the samples that fall in each bin. The first thing to do is produce the histogram. The samples can be checked to confirm normally distributed by comparing the mean, median and mode which should all be equal. The actual mean and standard deviation was 100.84 and 27.49 respectively. I created samples with a mean of 100 and standard deviation of 25, function RandNormalDist(100, 0.25). To produce my random normal samples I used VBA function RandNormalDist by Mike Alexander.
This tutorial will walk you through plotting a histogram with Excel and then overlaying normal distribution bell-curve and showing average and standard-deviation lines.
