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.

Bin Numbers

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.

Frequency Function in Excel

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.

Dynamic Array Formula

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.

Frequency Distribution

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.

Histogram in Excel

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.

No Extra Cell

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.

Countifs Function

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.