How to Use Excel to Find Cumulative Frequency Distribution

A histogram is a tool that visually depicts frequencies.

A frequency distribution is a statistical tool that analysts use to summarize large data sets, gain insight into the nature of data and have a basis for creating important graphs such as histograms. A histogram is a bar graph or visual representation of the relationship between classes or groups of data values on the horizontal scale and frequencies on the vertical scale. Cumulative frequency distribution provides a running total of frequencies for each class or group. Once the frequency for each class has been derived, use the SUM function and absolute cell referencing in a formula to automate the process of calculating cumulative frequency in Microsoft Excel.

Instructions

1. Create a data set in a range of cells in Excel. Highlight the range of cells and enter a descriptive name for the range in the name box below the ribbon on the upper left corner of the screen. Create columns for intervals, upper class limit, frequency and cumulative frequency.

2. Determine the number of interval classes you want to use for your frequency distribution. This is typically a number between five and 20.

3. Find the highest value in the range by using the MAX function with the named range as an argument. Find the lowest value in the range by using the MIN function with the named range as an argument.

4. Calculate the class width or intervals by dividing the difference of the highest value and lowest value (range of the data set) by the number of classes. Round up the result to obtain a convenient number.

5. Use the class width to manually enter information in the intervals and upper class limit column and name the range. Select the corresponding cells for the intervals in the Frequency column. Enter "=FREQUENCY(" (without quotes) in the formula bar.

6. Insert the range name of the data set as the first argument and the range name of the upper class limits as the second argument. Add a closing parenthesis to end the formula. Press the "Ctrl," "Shift" and "Enter" keys simultaneously to enter it as an array formula. Array formulas are used to perform complex calculations that can return one or more values. This formula goes through the data set and counts all numbers in the data set that are less than or equal to the upper class limit.

7. Select the cell in the Cumulative Frequency that corresponds to the first interval. Enter "=SUM(" (without quotes) in the formula bar. Add the absolute cell reference of the first frequency value as the first argument and relative reference to that same value as the second argument. Complete the formula with a closing parenthesis.

8. Drag the formula across the range of cells in the Cumulative Frequency for each interval to add the values. You can now use this information to prepare a histogram.