ExcelFunctions.net

Search Site:

For a supplied array of numeric values, the Excel Frequency function returns the number 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.

Cells A2 - A11 of the above spreadsheet on the left contain the ages of a group of children.

The Excel Frequency function (entered into cells C2 - C4 of the spreadsheet) has been used to count the number of children falling into three different age ranges, which are specified by the bins_array that is specified in cells B2 - B3 of the spreadsheet.

Note that the bins_array values specify the __maximum__ values for the first two age ranges. Therefore, in this example, the ages are to be split into the ranges 0-4 years, 5-8 years and 9 years+.

As shown in the formula bar, the format of the Frequency function in this example is:

=FREQUENCY( A2:A11, B2:B3 )

Note that the curly braces surrounding the function indicate that it has been entered as an Array Formula.

The Frequency function can also be used with decimal values.

Cells A2-A11 of the above spreadsheet on the right show the height (in meters) of a group of 10 children (rounded to the nearest cm).

The Frequency function (entered into cells C2 - C5) is used to show 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

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 the function has been supplied with the 3 bin_array values 1.0, 1.2 and 1.4 (stored in cells B2:B4).

As shown in the formula bar, the format of the Frequency function is:

=FREQUENCY( A2:A11, B2:B4 )

Again, the curly braces surrounding the function show that it has been entered as an Array Formula.

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

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