Statistical Distributions in Excel [spreadcheats]

[Lin Weidong]

Statistical Distributions in Excel [spreadsheets]

Just follow the below 2 steps to create statistical distribution / frequency of any set of values using excel. Also, download the statistical distributions example workbook and play with it.

 

1. Define the bands for distribution

Assuming the test scores range from 0 to 100, you can define score bands like 10,20,30,40,50,60,70,80,90,100

2. Create a frequency formula and array enter it in to the spreadsheet

Assuming the test scores are in the range B6:B105 and bands are in the range c6:c15:

First select the cells D6:D16 (10 cells, 1 each for the frequency between 0-10, 10-20, 20-30, … 90-100) and then enter the FREQUENCY() formula. See below:

http://chandoo.org/wp/2009/06/01/statistical-distributions/