The FREQUENCY function returns an array of how often data values occur within a collection of interval values.
FREQUENCY(data-values, interval-values)
data-values: A collection containing the values to be evaluated. data-values must contain number values, date/time values, or duration values. All values should be of the same value type.
interval-values: A collection containing the interval values. interval-values must contain values of the same value type as the values in the data-values collection.
Notes
FREQUENCY determines the number of values in data-values that fall within each interval. The interval array is easiest to understand if it is arranged in ascending order. The first frequency will be the count of those values that are less than or equal to the lowest interval value. All other frequency values, except the last, will be the count of those values that are greater than the immediately lower interval value and less than or equal to the current interval value. The final frequency value will be the count of those data values that are greater than the largest interval value.
The values returned by the function are contained in an array. One method of reading the values in the array is to use the INDEX function. You can wrap the FREQUENCY function within the INDEX function: =INDEX(FREQUENCY(data-values, interval-values), x) where x is the desired interval. Remember that there will be one more interval than there are interval-values.
Example |
|---|
Suppose the following table contains the test scores of 30 students who recently took an exam you administered. The scores are in cells A1:E6. Suppose further that the minimum passing grade is 65 and that the lowest score for other grades are as given (indicated as % in the table). In order to facilitate building the formulas, an “F” (below “Gr.” in Column C) is represented by 1 and an “A” by 5. |
A | B | C | D | E | |
|---|---|---|---|---|---|
1 | 55 | 75 | 90 | 65 | 91 |
2 | 33 | 88 | 74 | 97 | 77 |
3 | 83 | 76 | 89 | 93 | 92 |
4 | 74 | 96 | 51 | 87 | 81 |
5 | 99 | 100 | 82 | 86 | 76 |
6 | 84 | 85 | 61 | 74 | 78 |
7 | % | Gr. | |||
8 | 65 | 1 | |||
9 | 74 | 2 | |||
10 | 83 | 3 | |||
11 | 91 | 4 | |||
12 | 5 |
=INDEX(FREQUENCY($A$1:$E$6, $B$8:$B$11), C8) returns 5, the number of students who received an “F” (score of 65 or less). This formula can be entered in cell D8 and then filled down to cell D12. The resulting values returned for grades of “D” to “A” are 3, 8, 8, and 6, respectively. |