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. 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.

Syntax: 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.


Frequency Function Error

If you get an error from the Excel Frequency Function, this is likely to be the #N/A error occurs if the array formula is entered into a range of cells that is too large

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>



clearPost Comment