Creating the frequency
distribution table in
Excel
1. Decide on the number of intervals you would like to use.
2. Find the interval width (using the formula above).
3. Start your 1st interval at the lowest value in your dataset.
4. Finish your 1st interval at the lowest value + the interval width. ( = start_interval_cell
+ interval_width_cell )
5. Start your 2nd interval where the 1st stops (that’s a formula as well - just
make the starting cell of interval 2 = the ending of interval 1)
6. Continue in this way until you have created the desired number of intervals.
7. Count the absolute frequencies using the following COUNTIF
formula: =COUNTIF(dataset_range,”>=“&interval start) -
COUNTIF(dataset_ range,”>“&interval end).
8. In order to calculate the relative frequencies, use the following
formula: = absolute_frequency_cell / number_of_observations
9. In order to calculate the cumulative frequencies:
I. The first cumulative frequency is equal to the relative frequency
II. Each consequitive cumulative frequency = previous cumulative
frequency the respective relative frequency
Note that all formulas could be found in the lesson Excel files and the solutions of
the exercises provided with each lesson
Creating a histogram
in Excel
1.
Choose your data
2. Insert -> Charts -> Histogram
3. To change the number of bins (intervals):
1. Select the x-axis
2. Click Chart Tools -> Format -> Axis options
3. You can select the bin width (interval width),
number of bins, etc.
