Exceljet

Quick, clean, and to the point

Excel FREQUENCY Function

Excel FREQUENCY function
Summary 

The Excel FREQUENCY function returns a frequency distribution, which is a summary table that shows the frequency of each value in a range. FREQUENCY returns multiple values and must be entered as an array formula with control-shift-enter.

Purpose 
Get the frequency of values in a data set
Return value 
A vertical array of frequencies
Syntax 
=FREQUENCY (data_array, bins_array)
Arguments 
  • data_array - An array of values for which you want to get frequencies.
  • bins_array - An array of intervals ("bins") for grouping values.
Usage notes 

FREQUENCY counts how often values occur in a set of data. You can use Excel's FREQUENCY function to create a frequency distribution - a summary table that shows the frequency (count) of each value in a range.  It returns a vertical array of numbers that represent frequencies, and must be entered as an array formula with control + shift + enter.

For example, with data in A1:A100, and bins in B1:B5, you would select C1:C5 and enter a formula like this:

{=FREQUENCY(A1:A100,B1:B5)}

Curly braces added by Excel automatically when formula is array-entered.

Notes:

  1. FREQUENCY returns multiple values and must be entered as an array formula with control-shift-enter.
  2. FREQUENCY always returns an array with one more item than the bins_array. This is by design, to catch any values greater than the largest interval in the bins_array.
  3. Each bin shows a count of values up to and including bin value, excluding values already accounted for

To create a frequency distribution using FREQUENCY:

  1. Enter numbers that represent the bins you want to group values into
  2. Make a selection the same size as the range that contains bins, or one greater if want to include the extra item
  3. Enter the FREQUENCY function as an array formula using Control+Shift+Enter

Excel Formula Training

Formulas are the key to getting things done in Excel. In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Instant access. See details here.