Frequency Function Explained..!

Hi one and all,
This time I have tried to Explain the Frequency Function.

1. How Frequency Function works with Non Duplicate, Ascending Bin Array Values

Frequency Function calculates how often values occur within a range of values, and then returns a vertical array of numbers. For example, use FREQUENCY to count the number of test scores that fall within ranges of scores. Because FREQUENCY returns an array, it must be entered as an array formula.

Syntax

FREQUENCY(data_array,bins_array)

Data_array   is an array of or reference to a set of values for which you want to count frequencies. If data_array contains no values, FREQUENCY returns an array of zeros.

Bins_array   is an array of or reference to intervals into which you want to group the values in data_array. If bins_array contains no values, FREQUENCY returns the number of elements in data_array.

Remarks

1. The number of elements in the returned array is one more than the number of elements in bins_array. The extra element in the returned array returns the no. of values above the highest interval. 

2. FREQUENCY ignores blank cells and text in Data Array and Bin Array both.

3. FREQUENCY Function ignores Duplicates in the Bin_Array and treats only first occurrence.

4. FREQUENCY Function returns an array so must be entered as an array formula.

The formula I have used is

=INDEX(FREQUENCY($A$2:$A$11,$B$2:$B$4),ROW($A1))

FREQUENCY($A$2:$A$11,$B$2:$B$4) portion returns an array of frequency i.e. {2;3;4;1} and Index Function lists the these values.

Remarks:-

1. Each Bin Value in Bin_Array is the Upper limit of the interval, in which FREQUENCY Function distributes the Data_Array Values.

2. Lower limit is the Next Lower Number, in comparison to Upper Limit, present in the Bin Array (Bin Array Value).

3. That’s how FREQUENCY Function calculates range of the Class interval.

Here is how these values in array {2;3;4;1} are being calculated.

Class Intervals				L	U	Frequency		Remarks
Less than and Equals 2				<=	2	2	(1,2)	No lower number is present in comparison to 2 in the bin array.
More than 2 but Less than and equal to 5				2-	5	3	(3,4,5)	2 is the next lower number in comparison to 5. Hence taken as  Lower Limit.
More than 5 but Less than and equal to 9				5-	9	4	(6,7,8,9)	5 is the next lower number in comparison to 9. Hence taken as Lower Limit.
Greater than 9				>=	9	1	(10)	This condition leads to add an extra count element in the  returned array.

I believe till now you will be knowing that how it works.

Now comes the SECOND PART.

The Second Part actually deals with Bin_Array Parameter and how actually FREQUENCY function treats the Values in Bin_Array.

In the previous example, the Bin_Array Values were in Ascending order and were Unique. But Now, I am disturbing their order and replicating some values to show you how FREQUENCY Function treats them while calculating Resultant Array

I have used the same formula but have adjusted Bin_Array to add one more cell.

Remarks (Repeated)

1. FREQUENCY Function ignores Duplicates in the Bin_Array and treats only first occurrence.

2. Each Bin Value in Bin_Array is the Upper limit of the interval, in which FREQUENCY Function distributes the Data_Array Values.

3. Lower limit is the Next Lower Number, in comparison to Upper Limit, present in the Bin Array. (Bin Array Value).

4. That’s how FREQUENCY Function calculates range of the Class interval.

Calculation of Class interval				L	U	Frequency		Remarks
More than 2 but Less than and equal to 3				2-	3	1	(3)	2 is the next lower number in comparison to 3. Hence taken as  Lower Limit.
less than and equal to 2				<=	2	2	(1,2)	No lower number is present in comparison to 2 in the bin array.
More than 3 but Less than and equal to 7				3-	7	4	(4,5,6,7)	3 is the next lower number in comparison to 4. Hence taken as  Lower Limit.
less than and equal to 2				<=	2	0	(-)	The frequency of this CI is Zero as CI is Repeating.
Greater than and equal to 7				>=	7	3	(8,9,10)	This condition leads to add an extra count element in the  returned array.

This is how, I believe, FREQUENCY function performs.
If you all have anything to suggest, correct or add then do comment.
Regards,