FREQUENCY function

The FREQUENCY function in Excel calculates how often values occur within a range of values, which is particularly useful for creating histograms or analyzing the distribution of data. It returns an array of frequencies that represent the count of values within specific bins or intervals. This function is often used in data analysis to understand the spread and distribution of data points.

Syntax:

FREQUENCY(data_array, bins_array)

Arguments:

• data_array: The range or array of values for which you want to calculate the frequency distribution.
• bins_array: The range or array of bin values that define the intervals or categories. These are the upper boundaries of the bins that group the values in data_array.

How It Works:

• The FREQUENCY function counts how many values in the data_array fall within each bin defined in the bins_array. Each bin includes values less than or equal to the corresponding bin value.
• The result is an array where each element represents the count of values in the corresponding bin. The last bin captures all values greater than the last value in the bins_array.

Important Note: The FREQUENCY function returns an array of results, so you need to either enter the function as an array formula or use it in a single cell with the correct range to return multiple results.

Example:

Suppose you have a set of test scores and you want to count how many scores fall into different grade ranges:

• Test Scores (data_array in cells A2:A10): {85, 92, 78, 88, 93, 70, 82, 91, 85, 79}
• Grade Ranges (bins_array in cells C2:C4): {80, 90}

To calculate how many scores fall below 80, between 80 and 90, and above 90, use the following formula:

=FREQUENCY(A2:A10, C2:C4)

The result will return an array of values that indicate the number of scores within each of the following ranges:

• Less than or equal to 80
• Between 80 and 90
• Greater than 90

Expected Output:

• The first bin (less than or equal to 80) will return the count of scores less than or equal to 80.
• The second bin (between 80 and 90) will return the count of scores between 80 and 90.
• The third bin (greater than 90) will return the count of scores greater than 90.

Key Points:

• The FREQUENCY function is array-based, meaning it outputs an array of results.
• To use it properly, you must either:
  • Press Ctrl + Shift + Enter (in older Excel versions) when you enter the formula to execute it as an array formula.
  • Alternatively, use modern Excel versions that handle array formulas automatically (Excel 365 or Excel 2021+).
• The function is often used in histograms, which provide a visual representation of the frequency distribution of data.
• The last bin in the bins_array includes all values that are greater than the last bin value.

Use Cases:

• Histogram Creation: Use the FREQUENCY function to group data into intervals and then plot a histogram to visualize the distribution.
• Data Distribution Analysis: Analyze how data points are distributed within specific ranges, useful for understanding trends and patterns.
• Exam Scores: Group student scores into grade ranges to understand how many students fall into each grade category.

Notes:

• The FREQUENCY function automatically includes the last bin as the category for all values greater than the last bin value in the bins_array. For example, if the last bin is 90, it will count all values greater than 90.
• The bins_array should be sorted in ascending order for accurate results.

Example in Array Formula:

If you’re working in Excel versions before 365 or 2021, after typing the formula:

=FREQUENCY(A2:A10, C2:C4)

You will need to press Ctrl + Shift + Enter to enter it as an array formula, which will output an array of frequencies.