How to Use the FREQUENCY Function in Google Sheets to Calculate Binned Data Distributions

Analyzing numerical datasets—such as customer satisfaction ratings, exam scores, sales transaction sizes, or employee ages—requires understanding how data points are distributed across specific intervals or brackets. Calculating these distribution counts using repetitive COUNTIFS formulas for every individual bracket is tedious and prone to boundary overlapping errors. In Google Sheets, the native FREQUENCY function automatically groups raw numbers into defined bins and spills the distribution count across an entire vertical array.

Understanding the Syntax of FREQUENCY

The FREQUENCY function calculates how often values occur within specified interval ranges:

• =FREQUENCY(data, bins)
• data: The range or array of numerical values you want to analyze and count.
• bins: The range containing the upper thresholds for each interval bracket.

Setting Up Bins and Calculating Distributions

Suppose you have a list of test scores in cells A2:A100 ranging from 0 to 100. To group scores into standard academic brackets (0-59, 60-69, 70-79, 80-89, 90-100):

1. In column C, enter your upper bin thresholds: 59 in cell C2, 69 in C3, 79 in C4, 89 in C5, and 100 in C6.
2. Click in cell D2 alongside your first bin threshold.
3. Enter the formula:
   =FREQUENCY(A2:A100, C2:C6)
4. Press Enter. Google Sheets automatically spills the count across all bins.

How FREQUENCY Handles Bin Boundaries and Outliers

Understanding boundary logic ensures accurate statistical interpretation:

• Inclusive Upper Bounds: Each bin includes values greater than the preceding threshold and less than or equal to the current threshold.
• Automatic Overflow Bin: FREQUENCY always returns one additional count beyond your specified bins, capturing any data points that exceed your highest threshold.
• Pairing with Charts: The output array is formatted perfectly to serve as the direct data source for bar charts and distribution histograms.

Mastering the FREQUENCY function in Google Sheets streamlines exploratory data analysis, allowing analysts to bin numerical data cleanly without cumbersome helper logic.