FREQUENCY function in Excel calculates the frequency of occurrence of values in the range you specify. You can also use the COUNTIFS function to generate a frequency distribution.
| Join the channel Telegram of the AnonyViet 👉 Link 👈 |
How to use the Frequency function in Excel
1. First, enter the number of values into the range C4:C8.
2. Select the range D4:D9, enter the FREQUENCY function shown below (without the curly braces) and finish by pressing CTRL + SHIFT + ENTER.
Note: the formula bar indicates this is an array formula by enclosing it in curly braces {}. To delete this array formula, select the range D4:D9 and press Delete.
3. Hide column C and insert column with new content.
Explanation: 1 value is less than or equal to 20, 8 values are greater than or equal to 21 and less than or equal to 25,.. In step 2, we choose the range D4:D9 (instead of D4: D8 ). Therefore, the FREQUENCY function also counts numbers greater than 40.
4. You can also use the Analysis Toolpak to create charts.
5. Change bin. Select the range D4:D9 (no extra cells), enter the FREQUENCY function shown below (without the curly braces), and finish by pressing CTRL + SHIFT + ENTER.
Explanation: the final number of bins is greater than or equal to the maximum value (52). Therefore, we do not need an extra cell to count the number of values greater than 60.
6. You can also use the COUNTIFS function to generate a frequency distribution.
Explanation: the COUNTIFS function in Excel counts cells based on two or more conditions. The COUNTIFS function shown above has 2 range/condition pairs. The & operator combines “>=” with the value in cell C4 and “<=” with the value in cell D4. You can easily copy this formula to other cells.
