FREQUENCY

The FREQUENCY function is used to calculate the frequency distribution of data points in a set of values. This function helps in analyzing data distribution and identifying patterns or outliers within a dataset.

Syntax

=FREQUENCY(Data_Array, Bins_Array)

Data_Array The array of values for which you want to calculate frequency distribution.
Bins_Array The array of threshold values that define the intervals for grouping the data points.

About FREQUENCY

When you're faced with a dataset filled with various values and you want to gain insights into the distribution of those values, the FREQUENCY function in Excel comes to the rescue. This function allows you to segment your data into different intervals or bins, providing a clear picture of how frequently values fall into each bin. By organizing the data in this manner, you can easily identify trends, patterns, or anomalies that may exist within the dataset. This can be particularly useful in statistical analysis, research, or any scenario where understanding the distribution of values is key to drawing meaningful conclusions or making informed decisions.

Examples

Suppose you have a set of exam scores in cells A2:A11 (70, 85, 90, 65, 80, 75, 95, 70, 85, 90) and you want to calculate the frequency distribution of scores in the following bins: 60-69, 70-79, 80-89, 90-100. The FREQUENCY formula would be entered as an array formula (confirm with Ctrl + Shift + Enter): =FREQUENCY(A2:A11, {60,70,80,90,100})

Suppose you have a list of sales amounts in cells B2:B21 and you want to analyze the sales distribution in the following intervals: $0-$100, $101-$200, $201-$300, $301-$400. The FREQUENCY formula would be: =FREQUENCY(B2:B21, {0, 100, 200, 300, 400})

Questions

How does the FREQUENCY function help in data analysis?

The FREQUENCY function aids in data analysis by quantifying the distribution of values within predefined intervals or bins. This allows users to discern patterns, trends, or outliers within the dataset.

What happens if the Data_Array and Bins_Array are not the same length in the FREQUENCY function?

If the Data_Array and Bins_Array are not of equal length, Excel will likely produce erroneous results. Make sure to align the length of both arrays to ensure accurate frequency distribution calculations.

Why might you need to enter the FREQUENCY function as an array formula?

Entering the FREQUENCY function as an array formula is necessary when you want to calculate the frequency distribution for multiple data points simultaneously. This ensures that the results are displayed correctly across all data points.

Related functions

HISTOGRAM COUNT COUNTIF COUNTIFS SUMIF SUMIFS AVERAGE MEDIAN