How To Use Frequency Function In Excel
Frequency (data_array, bins_array)
It calculates how many values of a dataset fall in a particular interval.Generally it is used to group the values of a dataset in intervals.
It takes two arguments, one is a dataset and the other is a list of intervals.
Data_array: it is an array or a range of values for which you want the frequency. (Dataset)
Bins_array: it is an array or a range of intervals based on which you want to group the values of data_array. (Intervals)
It returns an array of numbers(count of frequencies)
The size of returned array will be more than one the size of bins_array,the extra element returns the count of elements in data_array that are greater than the max value of the bins_array
It must be entered as an array formula.
e.g.
We have a list of Students' scores and we want to group them in some intervals given below.
Here list of scores will be the data_array and bins_array will be upper limits of the intervals
Code:
Scores Intervals bins_array Results
30 0-20 20 0 (No element between 0 to 20)
97 21-40 40 4 (4 elements between 21 to 40)
62 41-60 60 5 (5 elements between 41 to 60)
45 61-80 80 4 (4 elements between 61 to 80)
94 7 (Here 7 is an extra element returns count of elements
32 that are greater than the largest value specified in the interval )
68
85
87
45
86
31
94
60
45
61
61
55
36
84
Order of bins_array elements does not matter.It treats it as a sorted array
e.g.
As in above example if we change the order of elements in bins array then the function returns same values against each element of bins_array as it would have if the bins_array was sorted
Code:
Unsorted Results
40 4
20 0
80 4
60 5
7
