Table of Contents
Cumulative frequency in Excel can be calculated by using the COUNTIFS function to count the number of times a certain value appears in a range of cells, then adding it to the number of times all lower values appear in the range. This will give you the cumulative frequency of that value. You can also use the SUMIFS function to accumulate the values of a range of cells and get the cumulative frequency of all values in the range.
A frequency table is a table that displays information about frequencies. Frequencies simply tell us how many times a certain event has occurred.
For example, the following table shows how many items a shop sold in different price ranges in a given week:
| Item Price | Frequency |
|---|---|
| $1 – $10 | 20 |
| $11 – $20 | 21 |
| $21 – $30 | 13 |
| $31 – $40 | 8 |
| $41 – $50 | 4 |
The first column displays the price class and the second column displays the frequency of that class.
It’s also possible to calculate the cumulative frequency for each class, which is simply the sum of the frequencies up to a certain class.
| Item Price | Frequency | Cumulative Frequency |
|---|---|---|
| $1 – $10 | 20 | 20 |
| $11 – $20 | 21 | 41 |
| $21 – $30 | 13 | 54 |
| $31 – $40 | 8 | 62 |
| $41 – $50 | 4 | 66 |
For example, the first cumulative frequency is simply equal to the first frequency of 20.
The second cumulative frequency is the sum of the first two frequencies: 20 + 21 = 41.
The third cumulative frequency is the sum of the first three frequencies: 20 + 21 + 13 = 54.
And so on.
The following example illustrates how to find cumulative frequencies in Excel.
Example: Cumulative Frequency in Excel
First, we will enter the class and the frequency in columns A and B:
Next, we will calculate the cumulative frequency of each class in column C.
In the image below, Column D shows the formulas we used:
To create the ogive chart, hold down CTRL and highlight columns A and C.
Then go to the Charts group in the Insert tab and click the first chart type in Insert Column or Bar Chart:
Along the top ribbon in Excel, go to the Insert tab, then the Charts group. Click Scatter Chart, then click Scatter with Straight Lines and Markers.
This will automatically produce the following ogive graph:
Feel free to modify the axes and the title to make the graph more aesthetically pleasing:
