Excel Formula Help - using the FREQUENCY function
Microsoft Excel provides the FREQUENCY function to summarise data into value groups. In this blog, we will again use the Baltimore City Employee Salaries data set for 2011 which is freely available on the city website.
The employee list for Baltimore City is extensive, running to 14454 entries in 2011.
Using the FREQUENCY function we can summarize, or categorise, the number of employees at each salary level.
The function works by cross referencing two value arrays, as can be seen by the syntax note in the image below, FREQUENCY(data _ array, bins _ array) (the example now has the Name and Agency columns hidden and has also standardised and sorted the remaining Annual Salary column from largest to smallest):
The data_array refers to the list of salary payments and the bins_array refers to the groupings of salaries.
It is important to understand how Excel uses the Bins data. Each Bins row will count the number of salaries higher than the previous figure and up to the figure in the row - not over. The next set of salaries will be counted from above that figure.
In other words, Bins row $39,999,99 will count all salaries greater than but not equal to $19,999.99, up to and equal to $39,999.99. Row $79,999.99 will count all salaries greater than but not equal to $39,999.99, up to and equal to $79,999.99, and so on.
We fist input the data_array. Placing out cursor in F2, we can select the relevant data in that column. With such a long list, it is easier to use the Ctrl-Shift-Down Arrow to select the data in the column, in this case F2:F14455.
Once that is entered, use a comma and select the bins_array which is here J6:J14.
IMPORTANT: This is a CSE or Array Formula, so instead of just hitting enter, we need to use Ctrl-Shift-Enter:
We can now get a good idea of the distribution of salary payments. We can clearly see that salaries located between $39,999.99 and £79,999.99 are the most common.
For more help with the FREQUENCY function and with making sense of large data sets, contact the Excel4Business experts.
