FREQUENCY Function in Excel

Syntax

The syntax of FREQUENCY function is stated as follows:

You are free to use this image on you website, templates, etc., Please provide us with an attribution linkHow to Provide Attribution?Article Link to be HyperlinkedFor eg:Source: FREQUENCY in Excel (wallstreetmojo.com)

The FREQUENCY Formula has the following mandatory arguments:

  • Data_array –It is an array or reference to a set of certain values whose frequencies we need to count.Bins_array–It is an array or reference to intervals into which you want to group the values in “data_array.”

This function returns an array of values. In Excel, it is used as an array formula using “CTRL+Shift+Enter” (For Mac: Press “Command+Shift+Enter”). Select the cell in which the output is required. Then type the FREQUENCY formula in Excel and enter the array formula.

Select cells –> Type formula –> Press “CTRL+Shift+Enter”

 

The Output of the Frequency Function

If “data_array” contains no values, the FREQUENCY function returns an array of zeros. If “bins_array” contains no values, the FREQUENCY function returns the total number of elements given in “data_array.”

Sometimes, it is required to understand the frequency distributionFrequency DistributionFrequency distribution refers to the repetitiveness of a variable, i.e., the number of times a variable occurs in a data set. In excel, it is a function to tabulate or graphically represent the recurrence of a particular value in a group or at an interval.read more of a given data rather than the data itself. For example, the age of individuals in a population varies greatly; hence you need to visualize it as frequencies. Similarly, marks obtained by each student in a class need clubbing in terms of frequencies to understand the overall performance of the class.

How to Use FREQUENCY Function in Excel?

The function is very simple and easy to use. Let us understand how it works with the help of some examples.

Example #1

We have numbers {1, 3, 2, 4, 6, 2, 3, 4, 5} in B3:B11 for which the frequency is to be calculated. 

Example #2

A survey was conducted. It collected data related to the height of the participants. The results are given in the following table.

  • Club the numbers into the intervals {2, 4, 6} in D3:D5. To calculate the frequency, first, select four cells E3:E6 and then use the following formula: “=FREQUENCY(B3:B11, D3:B5)” Then press “CTRL+Shift+Enter.” As the number of elements returned is one more than the number of elements in “bins_array,” you need to select four cells in this case. This will return the frequency of numbers given in B3:B11. The given output {3, 4, 2, 0} corresponds to the interval {2, 2-4, 4-6,6}.If you prefer to select only three cells instead of four, the count of “greater than 6” will be omitted (as the succeeding image indicates). The output of the FREQUENCY function is shown in the following image:

“=FREQUENCY(B3:B11, D3:B5)”

Now, calculate the frequency for the intervals mentioned below.

< 155

155-160

160-165

165-170

170

E4:E7 are showing the intervals {155, 160, 165, 170}.

Now, let us do the frequency calculation with the steps mentioned below.

  • Start by selecting five cells that are consecutive (4 + 1).

  • Enter the below-mentioned formula:

“=FREQUENCY(B4:B14, E4:E7)”

  • Press “CTRL+Shift+Enter.”

This returns the frequency of the height for the specified intervals (shown in the subsequent image).

Example #3

A list of IDs of students who have failed in one or more subjects in a class is mentioned below. The table also includes the subjects in which they have failed. Those who have failed in either one subject or more are considered as fail.  Find out the number of students who have failed.

To identify the number of students who have failed, use the following formula:

“=SUM(–(FREQUENCY(B4:B9, B4:B9) >0))”

It returns 4 as the number of failed students (as shown in the subsequent image).

Let us look at the details of the formula.

FREQUENCY(B4:B9, B4:B9) calculates the frequency of data B4:B9 using the interval B4:B9. It returns {1; 1; 2; 0; 2; 0; 0}.

FREQUENCY(B4:B9, B4:B9) >0 checks if the obtained frequency is greater than zero. It returns the logical value “true” if it is greater than zero, else “false.” It returns {TRUE; TRUE; TRUE; FALSE; TRUE; FALSE; FALSE}.

SUM(–(FREQUENCY (..) >0)) will sum up the “true” and return the number of unique values.

Example #4

The data of daily customer visits to a supermarket is shown in the table below. The time of their visit is also given. The data is displayed in cells B4:C39. We want to see at which time intervals the customers visited the most in the store. This will help us plan the employees’ working hours efficiently. The store opens at 11:00 a.m. and closes at 8:00 p.m.

Let us first decide on the time interval. We use the following intervals for the sake of simplicity:

  • 11:00 a.m.12:00 a.m.1:00 p.m.2:00 p.m.3:00 p.m.4:00 p.m.5:00 p.m.6:00 p.m.7:00 p.m.8:00 p.m.

  • Select the cells G4:G13 in the table, where the frequency is obtained.  The store closes at 8:00 p.m. Hence, we need not select the cell for >8:00 p.m., as it is zero in all the cases.

  • Now, enter the following formula:

“=FREQUENCY(B4:C39,G4:G13)”

  • Press  “CTRL+Shift+Enter.”

It returns the frequency of customer visits to the store. In this case, we observe the maximum customer visits between 5:00 p.m. – 6:00 p.m.

The FREQUENCY function calculates the number of times a value falls within the specified range of values. The output is a vertical array of numbers. For example, the FREQUENCY function counts the number of employee performance scores that fall within a range of scores.

The steps to do a frequency distribution using the FREQUENCY function are listed as follows: ● First, enter numbers that represent the bins in which we want to group values. ● Then make a selection resembling the same size as the range that contains bins, or one cell greater if we need to include the extra item. ● Enter the FREQUENCY function formula that is an array formula using “CTRL+Shift+Enter.”

The two arguments of the FREQUENCY formula include: ● Data_array – It refers to a range of cells containing numeric values. ● Bins_array – It refers to a range of cells holding bins values into which the numerical values are to be grouped.

Key Takeaways

  • The FREQUENCY function in Excel gives the frequency distribution of the given data (“data_array”) into the given intervals (“bins_array”).The FREQUENCY formula in Excel is entered as an array formula. To calculate frequency, a range of adjacent cells is selected into which the distribution is required to appear. To enter the FREQUENCY formula in Excel, press “CTRL+Shift+Enter” (for Mac, press “Command+Shift+Enter”).For “x” number of elements in the “bins_array,” select “x + 1” number of cells, while entering the FREQUENCY formula in Excel. The extra cell returns the number of values in “data_array,” which is greater than the third interval value. The FREQUENCY formula ignores blank cells and text.

FREQUENCY Excel Function Video

This has been a guide to the FREQUENCY Function in Excel. Here, we discuss how to use this function along with step by step examples and downloadable template. You may also look at these useful functions in Excel –

  • VBA Select CellVBA Select CellVBA select cell assists the users to pick any particular cell using various methods like Macro recorder, range object, select statement, CELLS property and cell reference.read moreHow to use INT Formula?How To Use INT Formula?INT or integer function in excel returns the nearest integer of a given number and is used when we have many data sets and each data in a different format.read moreCOS Excel FunctionCOS Excel FunctionCOS Excel function is a in-built trigonometric function in Excel that calculates the cosine value of a given number. In Excel, the angle is a number, and this function accepts only one argument, which is the input number.read moreMEDIAN in ExcelMEDIAN In ExcelMEDIAN function in Excel gives the median of a given set of numbers. MEDIAN Identifies the location of the center of a group of numbers in a statistical distribution.read more