Excel Frequency Formula

Excel Frequency Formula (Table of Contents)

• Frequency Formula in Excel
• How to Use Frequency Formula in Excel?

Frequency Formula in Excel

FREQUENCY formula is a prebuilt integrated function which is categorized under a STATISTICAL group of formula in excel.

It’s a statistical calculation that lets you know the count how many times a specific value fall within a specific range, e.g. Suppose if a table contains multiple age group of people, we can group them based on the age parameter, i.e. Age grouping (frequency of appearance for Different age groups i.e.

• Child (0-12 years),
• Adolescence (13-18)
• Adult (19-59)
• Senior Citizen (60 years and above)

The frequency formula in excel calculates or determines the Dataset’s frequency distribution, i.e. how many times that value appears within its range of values.

Definition of Frequency formula in Excel

• The frequency formula calculates or determines the supplied vertical array’s frequency distribution based on the Bins or intervals that we supply.
• The frequency formula counts the values in a range of cells or array that fall within a range or bin in excel.

The syntax for the Frequency formula in Microsoft Excel is:

The Frequency Formula in excel has two arguments which are as below:

• Data Array: It is an actual range or array value where you need to find out Dataset’s frequency distribution.
• Bins Array: It is a range or array of intervals (BINS) for grouping values.

The frequency formula should be entered as an array formula for multiple frequencies. If we want to find out multiple Bins or return multiple frequency values, it should be entered as an array formula with CTRL +SHIFT +ENTER option in excel.

How to Use Frequency Formula in Excel?

Frequency Formula in Excel is very simple and easy to use. Let’s understand the working of Frequency Formula in Excel with some examples.

Example #1 – To find out a single Frequency in excel

In the below-mentioned example, the Table contains a different number of age group entities. I need to find out the frequency of the age group under or equal to 10 years. Here Frequency formula counts how often values occur in a range or array in excel.

• Now, Let’s apply the frequency formula in cell C2. Select cell C2 where the frequency formula needs to be applied.

• Click the insert function (fx) button under the formula toolbar; a dialog box will appear, Type the keyword frequency in the Search for a Function box; the frequency will appear in select a Function box. Double click on the Frequency function.

• A dialog box appears where arguments for the frequency function needs to be filled or entered. i.e. =FREQUENCY (data_array, bins_array)

• Data Array: It is an actual range or array value where you need to find out Dataset’s frequency distribution; here, the range or array is from A2 to A18, So that select the column range. i.e. A2:A18.

• Bins Array: It is a reference or range or array of intervals (“BINS”) for grouping values.

As we want to find a single frequency here. I need to find out the frequency of age group under or equal to 10 years only here; therefore, we need to enter cell ref or age as 10 or B2. Click ok after entering the two arguments. =FREQUENCY (A2:A18,B2)

So the output will be as given below.

You can observe that frequency excel formula found 2 values or member where their age is less than or equal to 10 years of age in the above single frequency calculation. (Age > or = 10 years)

Example #2 – To Find Multiple Frequency in a data range

In the below-mentioned example, the Table contains a member with different age entities. I need to find out or calculate the frequency distribution of different age groups of 17 members aged between 9 to 80.

• Now, Let’s apply the frequency formula in cell E2. Select the cell E2 where the frequency formula needs to be applied.

• Click the Insert Function (fx) button under the formula toolbar, a dialog box will appear, Type the keyword frequency in the Search for a Function box, the frequency will appear in the Select a Function box. Double click on the Frequency function.

• A dialog box appears where arguments for the frequency function needs to be filled or entered. i.e. =FREQUENCY (data_array, bins_array)

• Data_Array: It is an actual range or array value where you need to find out Dataset’s frequency distribution; here, the range or array is from A2 to A18, So select the column range. i.e. A2:A18.

• Bins_Array: It is a reference or range or array of intervals (BINS) for grouping values.

As we want to find out multiple frequency values here. I need to find out the frequency distribution of four different age groups here; therefore, we need to enter four bins array values, So that the four bins array values or range will get selected, i.e. D2:D5. Click ok after entering the two arguments. FREQUENCY(A2:A18,D2:D5)

The result will be as given below.

 

Now, you can observe that only the first cell of the Frequency column has a value; the rest of the cells are blank.

To obtain the other values, select a cell range from E2 to E5. Once it is selected, Click inside the dataset of the frequency formula bar. Now, you can see, all the three range of values get selected in three different colors; now, press CTRL + SHIFT + ENTER for the array formula to apply.

Now, you can see the frequency result column; it displays all the missing values.

In the formula toolbar, you can observe the curly brackets for an array formula.

Things to Remember about Frequency Formula in Excel

• Frequency Formula ignores blank cells or text values in excel.
• If the Data_Array argument in the frequency formula does not contain any values in an array or range of cells, then Frequency returns an array of zeros.
• If the Bin_Array argument in the frequency formula does not contain any values in an array or range of cells, then Frequency returns the number of elements in the data_array.

Recommended Articles

This has been a guide to Frequency Formula in Excel. Here we discuss how to use Frequency Formula in Excel along with practical examples and a downloadable excel template. You can also go through our other suggested articles –

1. Excel Frequency Distribution
2. FREQUENCY Excel Function
3. VLOOKUP Function in Excel
4. Excel PRICE Function