Excel - Microsoft Excel FREQUENCY Function

Description

For a supplied array of numerical values, the Excel Frequency function returns the count of values that fall into specified ranges. 

Array Formulas 

To input an array formula, you need to first highlight the range of cells for the function result. Type your function into the first cell of the range, and press CTRL-SHIFT-Enter.

Go to the Excel array formulas page for more details. 

For example, if you hold data on a group of children, you could use the Excel Frequency function to count how many children fall into different age ranges. 

As the Frequency function returns an array of values (containing the count within each specified range), it must be entered as an array formula. 

The syntax of the Frequency function is : 

FREQUENCY( data_array, bins_array ) 

where the function arguments are as follows : 

The array returned by the Excel Frequency function will have one more entry than the supplied bins_array. This is shown in the examples below. 

Excel Frequency Function Examples

Example 1

Cells A2 - A11 of the spreadsheet on the left contain the ages of a group of children. The formula bar at the top of the spreadsheet shows the Excel Frequency function used to count the number of children falling into three different age ranges. 

The bins, specified in cells B2 - B3, specify the maximum values for the first two ranges. Therefore, in this example, the ages are to be split into the ranges 0-4 years, 5-8 years and 9 years+. 

The Frequency function in this example returns an array of length 3, and so it has been entered into cells C2-C4 of the spreadsheet. The format of the function is shown in the formula bar at the top of the spreadsheet - note that the curly braces indicate that the function has been entered as an Array Formula. 

Example 2

The Frequency function can also be used with decimal values. 

Cells A2-A11 of the spreadsheet on the right show the height (in meters) of a group of 10 children (rounded to the nearest cm). The Frequency function (shown in the formula bar of the spreadsheet) is used to produce an array, showing the number of children whose height falls into each of the ranges: 

0.0 - 1.0 meters
1.01 - 1.2 meters
1.21 - 1.4 meters
over 1.4 meters 

As we require the data to be split into 4 ranges, we need to supply 3 bin_array values (1.0, 1.2 and 1.4). 

The function results are shown in cells C2-C5 of the spreadsheet. 

Frequency Function Error

If you get an error from the Excel Frequency Function, this is likely to be the #N/A error: 

Common Error