Breaking News
Home / Tips and Tricks / How to Use the FREQUENCY Function in Excel

# How to Use the FREQUENCY Function in Excel  Excel's FREQUENCY feature lets you count how often values ​​are in specific ranges. For example, if you had the age of a group of people in your spreadsheet, you could find out how many people fall into different age groups. Let's take a look at the calculation of frequency distributions and with a slight change in the frequency percentages.

## Function of the FREQUENCY function

Use the FREQUENCY array function of Excel to calculate the frequency distribution of a data set. You enter the numeric dataset (these are the actual cells you use as the source), a list of bin thresholds (these are the categories to which you are sorting data), and then press Ctrl + Shift + Enter.

So, how could you use it? Well, here is a short example. For example, suppose you are a teacher with a spreadsheet that displays all of your student's numeric test scores. You can use the FREQUENCY function to find out how many students have received an A, B, C, D or F. The numeric test results are the dataset and the letter classes form the thresholds for the bin.

You would apply the FREQUENCY function to a list of student's test scores, and the function would count how many students received which letter score by comparing each test result to the range of values ​​that defines the various letter grades.

If you round the results to the nearest tenth of a percent, these ranges would apply:

F <= 59.9 < D <= 69.9 < C <= 79.9 < B <= 89.9 < A

Excel would give the C range a rating of 79.9 while a score of 98.2 would fall into the A range. Excel goes through the list of test results, categorizes each score, counts the total number of reviews that fall into each area, and returns a five-cell array that shows the total number of scores in each area.

The FREQUENCY function requires two arrays as inputs: a "Data_array" and a "Bins_array". Arrays are simply lists of values. The "data_array", like the numeric grades for students, must contain values ​​that Excel can compare to a set of thresholds defined in the "Bins_array", such as the letter grades in the same example.

## How to Determine Out-Frequency Percentages

That's all fine and a good, but what if instead of pure counts in the results, percentages would be displayed instead. For example, what percentage of our numbers fell between one and two.

To calculate the percentage frequency counts for each bin, you can use the Excel toolbar to change the array formula. Select all cells in the Results column and then add the following at the end of the formula in the toolbar:

`  / COUNT (B3: B20) `

The final formula should look like this:  = FREQUENCY ( B3: B20, C3: C20) / COUNT (B3: B20)

Now press Ctrl + Shift + Enter again. The new formula splits the individual elements of the results on array by the total number of values ​​in the "Data_array" column. The results are not automatically formatted as percentages, but that's easy to change. Switch to the "Home" menu and press the "%" key. The values ​​are now displayed in percent. For example, now you can see that 17% of the numbers in the column "Data_array" fell in the range 1-2. Best of all, now that the formula is in effect In the "Results" column, you can change the values ​​in the "Data_array" and "Bins_array" columns, and Excel automatically updates the results with updated values.

## Bypassing the Formulas Menu and Using the Toolbar

If you prefer typing and you know how to name columns and cells, you can always work around digging through the formulas menu just by typing functions directly in the toolbar of Excel, then press Ctrl + Shift + Enter.

How to Calculate Frequency Distribution Use the following syntax:

`  {= FREQUENCY ( Data_array   Bins_array )} `

Use this syntax instead to calculate frequency percentages:

`  {= FREQUENCY ( Data_array   Bins_array ) / COUNT ( Data_array )} `

Remember, this is an array formula. So you have to press Ctrl + Shift + Enter instead of just Enter. The presence of {braces} around the formula indicates that it was entered as an array formula.