قالب وردپرس درنا توس
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.

Consider an Example [1
9659003] In our example we calculate the frequency distribution and the percentage of the frequency of a set of 18 numbers between 0 and 10. It is just a simple exercise where we determine how many of these numbers are between one and two are two and three, and so on.

In our simple example table, we have two columns: data_array and bins_array.

The column "Data_array" contains the numbers and the column "Bins_array" contains the thresholds of the containers we use. Note that at the top of the Bins_array column, we left an empty cell to account for the number of values ​​in the result array, each containing a value other than Bins_array.

We are also creating a third column into which our results can go; We call it "results".

First, select the cells in which you want to insert the results. Now switch to the "Formulas" menu and click on the "More functions" button. From the drop-down menu, point to the "Statistical" sub-menu, scroll down a bit and click on the "FREQUENCY" function.

The "Function Arguments" window appears. Click in the "Data_array" box and mark the cells in the "Data_array" column (you can also enter the cell numbers if you want.)

If you receive an error message that you do not Only part of an array means that you have not selected all cells in the array. Click "OK" and press the Esc key.

To manipulate the formula of an array or delete the array, you must first mark all of the cells in the array.

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 is empty.

To see the other values, click in the Formula bar and press Ctrl + Shift + Enter.

The missing columns are now displayed in the results column.

You can see that Excel found four values ​​less than or equal to one (highlighted) in red) and also found the counts from the other number ranges. We have added a column "Result Description" to our spreadsheet, which allows us to explain the logic used to calculate each result.

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: [19659028] = 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.


Source link