Excel Frequency Function


Basic 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 :

data_array - The original array of values that the frequency is to be calculated for.
bins_array - An array of values, that specify the limits of the ranges that the data_array is to be split into.

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

Example of use of the Excel Frequency Function

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

Example of use of the Excel Frequency Function

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.



Further examples of the Excel Frequency function can be found on the Microsoft Office website.


Frequency Function Error

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

Common Error
#N/A -

Occurs if the array formula is entered into a range of cells that is too large

I.e. the #N/A error appears in all cells after the n'th cell (where n is the length of the bins_array + 1)
Return to the Excel Statistical Functions Page

Return to the List of All Built-In Excel Functions

Valid XHTML 1.0 Transitional Valid CSS!
Disclaimer   Privacy Policy
Copyright © 2008-2014 ExcelFunctions.net