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

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

Histogram in Excel

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.

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.

6. 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. We used the & operator to concatenate ">=" with the value in cell C4 and "<=" with the value in cell D4. You can easily copy this formula to the other cells.

14/14 Completed! Learn more about statistical functions >
Go to Next Chapter: Round   |   Become an Excel Pro: 300 Examples