Using the FREQUENCY function to group data

Mar 28, 2024 | Excel Hints and Tips

Do you find that if you plot your data against individual figures the results you get are not as useful as you would hope?Would grouping the data into blocks be more useful? That’s what the FREQUENCY function does.

This blog not only shows how the function works but provides real-life examples of where it can be used.

Syntax

The Frequency function calculates the number of times values occur within a range of values.  The results appear in a vertical list.

The function structure is shown here:

=FUNCTION(Data array, Bins array)

Data array – a set of values for which you want to count the frequencies

Bins array – the intervals you want to group the data array by

NOTE: The function ignores blank cells and text.

Example

In the following example the frequency function uses the data in cells A2 to A10 with the bins array in cells B2 to B4.

4 results are produced as follows:

There is 1 number that is less than or equal to 70, 2 numbers between 71 and 79, 4 numbers between 80 and 89 and 2 scores greater than 90.  You will always get 1 more result than the number of entries in the bins array.

NOTE: If you have a current version of Microsoft 365, then you can simply enter the formula in the top-left-cell of the output range, then press ENTER to confirm the formula as a dynamic array formula. The formula spills the results into a range of cells.  Otherwise, the formula must be entered as a legacy array formula by first selecting the output range, entering the formula in the top-left-cell of the output range, and then pressing CTRL+SHIFT+ENTER to confirm it. Excel inserts curly brackets at the beginning and end of the formula for you.

Uses of the FREQUENCY function

Grouping Data for use in Histograms

The Frequency function efficiently creates histograms by categorizing data into bins (groups) and counting the occurrences in each bin. This method streamlines the data into manageable sections, facilitating a clearer understanding.

e.g.  In an e-commerce scenario, analyzing purchase amounts by grouping them into bins (e.g., \$0-\$50, \$51-\$100, \$101-\$150) using Frequency helps discern patterns in transaction distribution.

Identifying trends and Outliers

Frequency aids in detecting trends and outliers within the data distribution. By visualizing this distribution, users can swiftly recognize patterns, peaks, or irregularities otherwise concealed in raw data.

e.g. Tracking monthly website traffic, and Frequency can categorize daily visits into bins, highlighting consistent patterns or sudden spikes for actionable insights into optimizing content strategy.

Dynamic Data Analysis

The way the frequency function works means that it adapts dynamically to growing or shrinking datasets ensuring the calculations produced do not need to be constantly tweaked.

e.g. Monitoring employee productivity based on completed tasks per week, Frequency accommodates changes in workforce size, providing accurate insights even as teams expand or contract.

Summary

The frequency function is a valuable addition to the function knowledge of individuals engaged in data analysis.  By grouping data together it becomes easier to see patterns than in the original  underlying data.