How To Use Frequency Function In Excel

**Transformer** · 04-09-2013, 10:40 AM

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