FREQUENCY

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. If you have Excel 365 or Excel 2021, simply select cell D4, enter the FREQUENCY function and press Enter. Bye bye curly braces.

Note: the FREQUENCY function, entered into cell D4, fills multiple cells. Wow! This behavior in Excel 365/2021 is called spilling.

4. 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.

5. You can also use the Analysis Toolpak to create a histogram.

6. 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.

7. 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.