Excel's FREQUENCY function enables you to count how many times values are within specific ranges. As an example, if you had the ages of a group of people in your spreadsheet, could calculate how many people are in different age ranges. Let's take a look at how to calculate the frequency distributions and, with a slight modification, the frequency percentages.
What does the FREQUENCY function do?
Excel's FREQUENCY matrix function enables you to calculate the frequency distribution of a data set. Provides the numeric data set (which are the actual cells you use as source), a list of location thresholds (those are the categories you are sorting the data into) and then press Ctrl + Shift + Enter.
Then, How could you use it? Well, here's a quick example. Suppose you are a teacher with a spreadsheet showing all your student's numerical test scores. You can use the FREQUENCY function to find out how many students got an A, B, C, D o F. The numerical test scores are the data set and the letter scores form your container thresholds.
I would apply the FREQUENCY function to a list of students' test scores, and the function would count how many students got what letter grade by comparing each test score to the range of values that define the different letter grades.
If you round the scores to the nearest tenth of a percent, these ranges will apply:
F <= 59,9 < D <= 69,9 < C <= 79,9 < B <= 89,9 < A
Excel would assign a score of 79,9 to rank C, while a score of 98,2 would enter rank A. Excel would check the list of test scores, I would categorize each score, would count the total number of scores that fall in each range and return an array with five cells showing the total number of scores in each range.
The FREQUENCY function needs two arrays as inputs: a “Data_array” and one “Bins_array”. Arrays are simply lists of values. The “Data_array” must contain values, as the numerical grades of the students, that Excel can compare to a series of thresholds defined in the “Bins_array”, like letter grades in that same example.
Let's see an example
For our example, we will calculate the frequency distribution and the frequency percentages of a set of 18 numbers between 0 Y 10. It is just a simple exercise in which we are going to determine how many of those numbers are between one and two, between two and three and so on.
In our simple example spreadsheet, we have two columns: Data_array y Bins_array.
The spine “Data_array” contains the numbers, and the column “Bins_array” contains the thresholds of the containers that we will use. Note that we have left a blank cell at the top of the column “Bins_array” to account for the number of values in the result matrix, which will always contain a value more than the “Bins_array”.
We are also going to create a third column where our results can go; we call it “Results”.
First, select the cells where you want the results to go. Now switch to the menu “Formulas” and click the button “More features”. In the drop-down menu, point to the submenu “Statistics”, scroll down a bit, and then click the function “FREQUENCY”.
The function arguments window will appear. Click on the box “Data_array” and then highlight the cells in the column “Data_array” (you can also type the cell numbers if you prefer).
If you get an error message telling you that you cannot edit only part of an array, it means you did not select all cells in the array. Click on “To accept” and then press the esc key.
To edit the formula of an array or remove the array, should highlight everybody cells of the array first.
Now, click on the box “Bins_array” and then select the full cells in the column “Bins_array”.
Click the button “To accept”.
You will see that only the first cell in the column “Results” has a value, the rest are blank.
To see the other values, click inside the bar “Formula” and then press Ctrl + Shift + Enter.
The Results column will now show the missing values.
You can see that Excel found four values that were less than or equal to one (highlighted in red) and also found the counts of each of our other number ranges. We have added a column of “Result description” to our spreadsheet so we can explain the logic that Excel uses to calculate each result.
How to calculate frequency percentages
That's very good, but what if instead of raw counts in the results, we would like to see percentages? What percentage of our numbers fell between one and two, as an example?
To calculate the frequency percentages for each bin, We can modify the array formula using Excel's function bar. Highlight all cells in the column “Results” and then add the following to the end of the formula on the function bar:
/COUNT(B3:B20)
The final formula should look like this:
=FREQUENCY(B3:B20,C3:C20)/COUNT(B3:B20)
Now, presione Ctrl + Shift + Enter again.
the new formula divides each element in the results array by the total count of values in the column “Data_array”.
Results are not automatically formatted as percentages, but that's pretty easy to change. Switch to menu “Beginning” and then press the button “%”.
Values will now appear as percentages. Then, as an example, now you can see that the 17% of the numbers in the column “Data_array” fell into the range 1-2.
Best of all, now that the formula is in place in the column. “Results”, you can modify any of the values in the columns “Data_array” Y “Bins_array” and excel will automatically update the results with the updated values.
Skip the formula menu and use the function bar
If you prefer to write and know how to name columns and cells, you can always skip the search in the menu “Formulas” simply by typing functions directly into the Excel function bar and then pressing Ctrl + Shift + Enter.
To calculate the frequency distribution, use the next syntax:
{=FREQUENCY(Data_array,Bins_array)}
To calculate frequency percentages, use this syntax instead:
{=FREQUENCY(Data_array,Bins_array)/COUNT(Data_array)}
Just remember this is an array formula, so you have to press Ctrl + Shift + Enter instead of just Enter. The presence of {brackets} around the formula indicates that it has been entered as an array formula.
