Using the Frequency Function

Whether you are in the corporate or academic world, there are times when you want to know investigate how frequent values within particular ranges occur. An excellent solution in Excel is the Frequency Function.

This unique tool works as an Array function that counts the number of values that occur in each specified interval for a given set of values and a given set of intervals (or “Bins”, as they are often referred).

The FREQUENCY Function syntax is as follows:

 FREQUENCY(data_array, bins_array)

The function is entered as an Array formula after you select a range of adjacent cells (C2:C5 in our example) into which you want the distribution to appear. After you select the data and bins arrays, press CONTROL+SHIFT+ENTER.

Please Note: 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 count of any values above the highest interval.

Example:

Formula Description and Result

=FREQUENCY(A2:A10,B2:B4)

1. Number of scores less than or equal to 75 (4)
2. Number of scores in the bin 76-84 (2)
3. Number of scores in the bin 85-94 (1)
4. Number of scores greater than or equal to 95 (2)

The Frequency Function can be a great boon to you regardless of the type of industry you work in.

Important Reminder: Be sure to Select your Entire Range (once again, C2:C5 in our example) where you want your results, and then enter the formula with the Array Keyboard Combination of CONTROL+SHIFT+ENTER.

Please send me an Email at rdelamartre@gmail.com if you have any problems setting up your table.

Happy Excelling All!