Excel Frequency Function
Function Description
For a supplied array of numeric values, the Excel Frequency function returns the number of values that fall into specified ranges.
For example, if you hold age data for a group of children, you could use the Excel Frequency function to count how many children fall into different age ranges.
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. |
Note: As the Frequency function returns an array of values (containing the count for each specified range), it must be entered as an array formula.
Inputting Array Formulas
To input an array formula in Excel, 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.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 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 (stored 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 formula for 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.
Example 2
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 bins_array values 1.0, 1.2 and 1.4 (stored in cells B2-B4).
As shown in the formula bar, the formula for 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.
For further examples of the Excel Frequency function, see 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). |