The FREQUENCY formula with example in Excel

The FREQUENCY function in Excel is used to count how often values occur within a range of values, and it returns a vertical array of numbers. This function is useful for statistical analysis and is typically used to create a frequency distribution.

Syntax

=FREQUENCY(data_array, bins_array)

• data_array: The array or range of data for which you want to count frequencies.
• bins_array: The array or range of bins into which you want to group the values.

Example

Suppose you have a list of exam scores for a group of students, and you want to create a frequency distribution to see how many students scored within specific score ranges.

Data

Scores
55
70
85
90
75
60
80
95
50
65

You want to count how many scores fall within the ranges:

• 0-59
• 60-69
• 70-79
• 80-89
• 90-100

Steps

1. Enter the scores into a column, e.g., A2

   .

2. Define the bins in another column, e.g., B2:B6:

   • B2: 59
   • B3: 69
   • B4: 79
   • B5: 89
   • B6: 100

3. Select the range where you want to display the frequency distribution, e.g., C2

   (one more than the number of bins, because the FREQUENCY function will return an array that includes the count of values above the highest bin).

4. Enter the FREQUENCY function as an array formula. In the formula bar, enter:

   =FREQUENCY(A2:A11, B2:B6)
   

   Press Ctrl+Shift+Enter to enter it as an array formula. Excel will enclose the formula in curly braces {}.

Result

The cells C2

will show the frequency distribution:

• C2: Number of scores <= 59
• C3: Number of scores between 60 and 69
• C4: Number of scores between 70 and 79
• C5: Number of scores between 80 and 89
• C6: Number of scores between 90 and 100
• C7: Number of scores > 100 (which should be 0 in this example)

Example with Data

Bin Range	Frequency
<= 59	2
60-69	2
70-79	2
80-89	2
90-100	2

The FREQUENCY function has automatically grouped the scores into the specified bins and counted how many scores fall into each bin.

This method allows you to quickly and effectively create a frequency distribution in Excel, which is helpful for data analysis and visualization.