FREQUENCY Function in Excel

Posted: April 16, 2013 by Transformer in Excel
Tags: , , ,

Frequency function calculates the number of values in a dataset that fall within a particular interval. Generally it is used to group the values of a dataset in intervals.
It requires two arguments, the first one is a dataset and the other is a list of intervals.

                         Frequency (data_array, bins_array)

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 data_array (Intervals)
This function returns an array of numbers (count of frequencies). The size of the returned array is one more than the size of bins_array, the extra element being the count of elements in the data_array that are greater than the max value of the bins_array. It must be entered as an array formula.

We have a list of students’ marks and we want to group them in intervals as given below. Here, the list of marks is the data_array and bins_array is the upper limits of the intervals.

Scores	 Interval   bins_array   Results
30	 <=20        20           0(No element <= 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(extra element that tells how many 
32			            elements lie outside the intervals)

The order of values given in bins_array is immaterial, as illustrated in the example below.
In the same dataset above, 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 done if the bins_array was sorted.

Unsorted (bins_array)               Results
  40                                   4
  20                                   0
  80                                   4
  60                                   5

Share your thoughts/feedback

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s