The FREQUENCY function in Excel calculates how often values occur within the ranges you specify in a bin table. You can also use the COUNTIFS function to create a frequency distribution.
1. First, enter the bin numbers (upper levels) in the range C4:C8.
2. Select the range D4:D9 (extra cell), enter the FREQUENCY function shown below (without the curly braces) and finish by pressing CTRL + SHIFT + ENTER.
Note: the formula bar indicates that 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 the column with the bin numbers (upper levels) and insert a column with proper bin labels.
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, etc. At step 2, we selected the range D4:D9 (instead of the range D4:D8). As a result, the FREQUENCY function also counts the number of values that are greater than 40.
4. You can also use the Analysis Toolpak to create a histogram.
5. Change the bin numbers. Select the range D4:D9 (no extra cell), enter the FREQUENCY function shown below (without the curly braces) and finish by pressing CTRL + SHIFT + ENTER.
Explanation: the last bin number is greater than or equal to the maximum value (52). As a result, we don't need an extra cell to count the number of values that are greater than 60.
6. You can also use the COUNTIFS function to create a frequency distribution.
Explanation: the COUNTIFS function in Excel counts cells based on two or more criteria. The COUNTIFS function shown above has 2 range/criteria pairs. The & operator joins “>=” with the value in cell C4 and “<=” with the value in cell D4. You can easily copy this formula to the other cells.