Excel Frequency Function Example

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 and finish by pressing CTRL + SHIFT + ENTER.

Note: the formula bar indicates that this is an array formula by enclosing it in curly braces {}. Do not type these yourself. To delete this array formula, select the range D4:D9 and press Delete.

Also See: Count numbers by range with COUNTIFS in Excel

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.

Also See: IF with wildcards in Excel

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

Also See: Basic text sort formula in Excel

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

Leave a Reply Cancel reply