For a supplied array of numerical values, the Excel Frequency function returns the count of values that fall into specified ranges.
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 :
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 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.
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:
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).
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:
Occurs if the array formula is entered into a range of cells that is too largeI.e. the #N/A error appears in all cells after the n'th cell (where n is the length of the bins_array + 1)