FREQUENCY function

Excel FREQUENCY function is a powerful tool that allows you to count how many times specific values appear in a dataset. It’s particularly useful for analyzing data distribution or creating histograms. Let’s break it down in simple terms so that even a high school student can understand.

Syntax

=FREQUENCY(data_array, bins_array)

Arguments

Here are the arguments for the FREQUENCY function:

data_arrayThe array or range of values to count frequencies for.
bins_arrayAn array or range of bin values (dividing values into intervals or categories).

How to Use

Using the FREQUENCY function involves a few steps:

  1. First, make sure you have your data ready in a column or a range.
  2. Create another range of values called bins_array, which represents the intervals or categories you want to count.
  3. Enter the FREQUENCY function in a cell, specifying the data_array and bins_array as its arguments.
  4. Instead of pressing Enter, you need to use Ctrl+Shift+Enter to make it an array formula (this is important).
  5. The result will be an array of frequencies for each bin, which you can use for further analysis or create a histogram.

Let’s illustrate this with an example. Suppose you have a list of test scores in A1:A10 and want to count how many students scored within certain grade ranges:

This formula will return the count of scores falling into each bin: less than 50, between 50 and 60, between 60 and 70, and so on.

Here’s another example: let’s say you have a list of ages in A1:A20, and you want to count the number of people in specific age groups:

This formula will give you the count of people in age groups: under 18, between 18 and 25, between 25 and 35, and so on.

Additional Information

The FREQUENCY function is a versatile tool for statistical analysis in Excel. It helps you quickly understand the distribution of data and can be used to create histograms, which provide visual representations of data patterns. Make sure to enter it as an array formula by using Ctrl+Shift+Enter for accurate results.

Tomasz Decker is an Excel specialist, skilled in data analysis and financial modeling.