The FREQUENCY function in Google Sheets is a powerful tool for analyzing data distributions. It calculates how often values occur within specified intervals, known as bins. This can be especially useful in statistical analyses, allowing users to visualize data trends and patterns effectively.

Syntax

FREQUENCY(data, bins)

• data: This is the array or range of values for which you want to determine the frequency distribution.
• bins: This refers to the array or range that defines the intervals into which you want to categorize the data.

Example #1

=FREQUENCY(A1:A10, B1:B5)

This function calculates how many values in the range A1:A10 fall into the bins specified in B1:B5. For example, if A1:A10 contains the values {1, 2, 2, 3, 4, 5, 5, 5, 6, 7} and B1:B5 contains {0, 2, 4, 6}, the result might be {2, 4, 2, 1}, indicating the count of numbers less than 2, between 2 and 4, between 4 and 6, and greater than 6.

Example #2

=FREQUENCY(C1:C10, D1:D5)

This function returns the number of occurrences of values in C1:C10 that fall within the bins specified in D1:D5. If C1:C10 holds {10, 20, 15, 25, 5, 30, 10, 25, 50, 20} and D1:D5 contains {0, 10, 20, 40}, then the output could be {2, 3, 2, 1}, showing counts of values in the corresponding ranges.

Example #3

=FREQUENCY(E1:E15, F1:F3)

This function evaluates how many values exist in E1:E15 that are categorized based on the ranges in F1:F3. Assuming E1:E15 has the values {3, 4, 8, 1, 10, 12, 9, 6, 7, 5, 11, 15, 14, 2, 13} and F1:F3 has specified thresholds {5, 10, 15}, the result might be {5, 6, 4}, reflecting the distribution of the data into those bins.

Error handling

• VALUE! This error occurs when non-numeric data is included in the data or bins array, making frequency calculation impossible.
• N/A Indicates that the function could not find the requested values due to incompatible ranges or if the bins are not defined correctly.
• REF! This error appears when the function refers to an invalid cell or range, meaning you need to check for any broken references.

Conclusion

The FREQUENCY function is an invaluable resource in Google Sheets for anyone looking to analyze numerical data. By allowing users to categorize and count values within specific ranges, it facilitates deeper insights and better decision-making. Leveraging this function can enhance your data analysis, enabling you to identify trends quickly and effectively.