Mastering the Frequency Formula: A Comprehensive Guide on Utilizing Excel's Countif Feature

Mastering the Frequency Formula: A Comprehensive Guide on Utilizing Excel’s Countif Feature

Quick Links

Excel’s FREQUENCY function lets you count how many times values fall within specific ranges. For example, if you had the ages of a group of people in your spreadsheet, you could figure out how many people fall into different age ranges. Let’s take a look at how to calculate frequency distributions and, with a slight modification, frequency percentages.

What Does the FREQUENCY Function Do?

Excel’s FREQUENCY array function lets you calculate a dataset’s frequency distribution. You provide the numerical dataset (that’s the actual cells you use as your source), a list of bin thresholds (that’s the categories into which you’re sorting data), and then press Ctrl+Shift+Enter.

So, how might you use it? Well, here’s a quick example. Say you’re a teacher with a spreadsheet that shows all your student’s numerical test scores. You could use the FREQUENCY function to figure out how many students got an A, B, C, D, or F. The numerical test scores are the dataset and the letter grades form your bin thresholds.

You would apply the FREQUENCY function to a list of student’s test scores, and the function would count how many students got which letter grade by comparing each test score to the range of values that define the different letter grades.

If you round scores 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 assign a score of 79.9 to the C range while a score of 98.2 would fall into the A range. Excel would go through the list of test scores, categorize each score, count the total number of scores that fall into each range, and return an array with five cells showing the total number of scores in each range.

The FREQUENCY function requires two arrays as inputs: a “Data_array” and a “Bins_array.” Arrays are simply lists of values. The “Data_array” needs to contain values—like the numerical grades for students—that Excel can compare to a series of thresholds defined in the “Bins_array”—like the letter grades in that same example.

Let’s Look at an Example

For our example, we will calculate the frequency distribution and frequency percentages of a set of 18 numbers between 0 and 10. It’s just a simple exercise where we’re going to determine how many of those numbers fall between one and two, between two and three, and so on.

In our simple example spreadsheet, we have two columns: Data_array and Bins_array.

The “Data_array” column contains the numbers, and the “Bins_array” column contains the thresholds of the bins we will use. Note that we’ve left a blank cell at the top of the “Bins_array” column to account for the number of values in the result array, which will always contain one more value than the “Bins_array.”

We’re also going to create a third column where our results can go; we’re naming it “Results.”

First, select the cells where you want the results to go. Now switch to the “Formulas” menu and click the “More Functions” button. On the drop-down menu, point to the “Statistical” submenu, scroll down a bit, and then click the “FREQUENCY” function.

The Function Arguments window pops up. Click in the “Data_array” box and then highlight the cells in the “Data_array” column (you can also type the cell numbers if you prefer).

If you receive an error message saying you cannot edit only part of an array, it means you didn’t select all of the cells of the array. Click “OK” and then hit the Esc key.

To edit the formula of an array or delete the array, you must highlight all of the cells of the array first.

Now, click in the “Bins_array” box and then select the filled cells in the “Bins_array” column.

Click the “OK” button.

You will see that only the first cell of the “Results” column has a value, the rest are blank.

To see the other values, click inside the “Formula” bar and then press Ctrl+Shift+Enter.

The Results column will now display 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’ve added a “Result Description” column to our spreadsheet so that we can explain the logic Excel used to calculate each result.

How to Figure Out Frequency Percentages

That’s all well and a good, but what if instead of raw counts in the results, we wanted to see percentages instead. What percentage of our numbers fell between one and two, for example.

To calculate the frequency percentages of each bin, we can alter the array formula using Excel’s Function Bar. Highlight all of the cells in the “Results” column and then add the following to the end of the formula in the Function Bar:

/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 divides each element of the Results array by the total count of values in the “Data_array” column.

The results are not automatically formatted as percentages, but that’s easy enough to change. Switch to the “Home” menu and then press the “%” button.

The values will now appear as percentages. So, for example, you can now see that 17% of the numbers in the “Data_array” column fell in the 1-2 range.

Best of all, now that the formula is in place in the “Results” column, you can alter any of the values in the “Data_array” and “Bins_array” columns and Excel will automatically refresh the results with updated values.

If you prefer typing and know your way around naming columns and cells, you can always bypass digging through the “Formulas” menu by simply typing functions directly into Excel’s Function Bar and then pressing Ctrl+Shift+Enter.

To calculate frequency distribution, use the following syntax:

{=FREQUENCY(Data_array,Bins_array)}

To calculate frequency percentages, use this syntax instead:

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

Just remember that this is an array formula, so you must press Ctrl+Shift+Enter instead of just Enter. The presence of {curly brackets} around the formula indicates that it has been entered as an array formula.

| | Mastering Excel Functions | | |
| —————————- | —————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————— | |
| Functions | AVERAGE · CONCATENATE · COUNT · COUNTIF · DATEDIF · FILTER · FREQUENCY · FV · HYPERLINK · IF · IFS · IMAGE · INDEX · IS · LEN · MATCH ·MEDIAN · RAND · ROUND · RRI · SORT · SQRT · SUBSTITUTE · SUBTOTAL · SUM · SUMIF · TODAY · TRIM · TRUNC · VLOOKUP · WEEKDAY · XLOOKUP · YEAR | |
| Types | Basic · Budgeting · Data Entry · Logical · Text · Time and Date | |
| Explained | Copying Formulas · Evaluating Formulas · Finding Functions · Fixing Formula Errors · Functions vs Formulas · Comparing Lookup Functions · Locking Formulas · Structuring Formulas · Translating Formulas | |

Win11 Tips