Excel Frequency Formula (Table of Contents)
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)
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
- Frequency formula calculates or determines the frequency distribution of supplied vertical array based on the Bins or intervals that we supply.
- 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.
Frequency formula should be entered as an array formula for multiple frequencies or if we want to find out multiple Bins or to 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.
4.8 (2,410 ratings)
View Course
Example #1 – To find out a single Frequency in excel
In the below-mentioned example, 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 frequency formula in cell C2. Select cell C2 where the frequency formula needs to be applied.
- Click the insert function (fx) button under 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 Frequency function.
- A dialog box appears where arguments for 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.
In the above single frequency calculation, 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. (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 frequency formula in cell E2. Select the cell E2 where frequency formula needs to be applied.
- Click the Insert Function (fx) button under 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 Frequency function.
- A dialog box appears where arguments for 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)
Note: Different age group is split into 4 age groups so that it will be four bins_array values.
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 Data_Array argument in frequency formula does not contain any values in an array or range of cells, then Frequency returns an array of zeros.
- If Bin_Array argument in frequency formula does not contain any values in an array or range of cells, then Frequency returns the number of elements in 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 downloadable excel template. You can also go through our other suggested articles –
