The Frequency in statistics is the number of items occurring in a given data category.
In Excel, you can use FREQUENCY function to count how often values occur within a range of values.
Formula:
= FREQUENCY(data_array, bins_array)
Explanations:
– Data_array is required, which is the data array you need to count frequency;
– Bins_array is required, which is the intervals you want to group the values in. When bins_array contains no value, the FREQUENCY function will return to the number of data.
Cautions:
The FREQUENCY function only counts numbers and does not count the blank cells or the cells with text.
To have the frequency for each group, you will need to press "Ctrl + Shift + Enter" at the end.
Example: To count the income frequency based on the 3 categories ($60,000, $80,000 and $100,000) ?
= FREQUENCY(C2:C9,D2:D4)
Explanations:
In this example, we need to count salary data in column E into 4 groups:
– Less than or equal to $60,000,
– Greater than $60,000 but less than or equal to $80,000,
– Greater than $80,000 but less than or equal to $100,000, and
– Greater than $100,000.
Steps to Have the Results:
Step 1: Create a helper column with boundaries: $60,000, $80,000, $100,000.
Step 2: Type formula =FREQUENCY(C2:C9,D2:D4) in a blank cell to have the frequency of the first group;
Step 3: Highlight 4 cells (because there are 4 groups) next to the first cell;
Step 4: Press F2, you will see the first cell becomes the formula;
Step 5: Press Ctrl + Shift + Enter key to have the frequency for each group.
There is 1 value less than $60,000, 3 values between $60,000 and $80,000, 2 values between $80,000 and $100,000, and 2 values are greater than $100,000.