To find cumulative frequency in Excel, you primarily use the SUM function with a clever combination of absolute and relative cell references, allowing you to sum a growing range of frequencies automatically.
Understanding Cumulative Frequency
Cumulative frequency represents the running total of frequencies. It helps in understanding how many data points fall below a certain value or category. For instance, if you have frequencies for different age groups, the cumulative frequency tells you how many people are younger than or equal to a certain age group.
Step-by-Step Guide to Calculate Cumulative Frequency
Let's assume your raw frequencies are listed in Column B, starting from cell B2. We'll calculate the cumulative frequency in Column C, starting from cell C2.
1. Set Up Your Data
Organize your data with one column for categories/classes and another for their respective frequencies.
| Category | Frequency | Cumulative Frequency |
|---|---|---|
| Item 1 | 5 | |
| Item 2 | 12 | |
| Item 3 | 8 | |
| Item 4 | 15 | |
| Item 5 | 10 |
(Assuming frequencies are in B2:B6)
2. Enter the Formula for the First Cumulative Frequency
- Select the cell where you want the first cumulative frequency to appear (e.g., C2).
- Input the following formula:
=SUM($B$2:B2)$B$2: The dollar signs ($) make this an absolute reference. This means that when you copy this formula to other cells,B2will always remain the starting point of your sum range.B2: This is a relative reference. When you copy the formula down, this part will change toB3,B4,B5, and so on, extending the sum range to include the current row's frequency.
- Press
Enter. The first cumulative frequency will be the same as the first frequency. For instance, if B2 contains '5', C2 will also display '5'.
3. Drag the Fill Handle Down
- Select the cell where you just entered the formula (e.g., C2).
- Locate the small square at the bottom-right corner of the selected cell. This is the fill handle.
- Click and drag the fill handle downwards across the cells where you want to calculate the remaining cumulative frequencies (e.g., down to C6).
As you drag, Excel automatically adjusts the formula:
- In cell C3, the formula will become
=SUM($B$2:B3), correctly summing the frequencies from B2 to B3. - In cell C4, it will be
=SUM($B$2:B4), summing frequencies from B2 to B4, and so on.
This method efficiently calculates the cumulative frequency for each subsequent row by adding the current frequency to the sum of all preceding frequencies.
Example Calculation:
Following the steps above for our example data:
| Category | Frequency (B) | Cumulative Frequency (C) | Formula in Column C |
|---|---|---|---|
| Item 1 | 5 | 5 | =SUM($B$2:B2) |
| Item 2 | 12 | 17 | =SUM($B$2:B3) |
| Item 3 | 8 | 25 | =SUM($B$2:B4) |
| Item 4 | 15 | 40 | =SUM($B$2:B5) |
| Item 5 | 10 | 50 | =SUM($B$2:B6) |
This table clearly illustrates how the SUM function with absolute and relative references dynamically expands the range to calculate the running total, providing the exact cumulative frequency.
