How to use FREQUENCY Function in Excel

The Excel FREQUENCY function returns a frequency distribution, which is a summary table that shows the frequency of each value in a range. FREQUENCY returns multiple values and must be entered as an array formula with control-shift-enter.

Syntax:= FREQUENCY (data_array, bins_array)

The FREQUENCY function syntax has the following arguments:

1. Data_arrays (It is a required argument) – This is an array or reference to a set of values for which you want to count frequencies.
2. Bins_array (It is a required argument) – This is an array of intervals (“bins”) for grouping values.

Example: Let’s look at some Excel FREQUENCY function examples and explore how to use the FREQUENCY function as a worksheet function in Microsoft Excel:

Example 1:

Suppose you carried out a survey and collected the data of height as shown below.

Now, you want to calculate the frequency of height in the following intervals:

< 155

155-160

160-165

165-170

> 170

The intervals {155, 160, 165, 170} are given in D2:D5

To calculate the frequency, first select five consecutive cells (4 + 1).

Then, enter the following syntax:

Syntax:  =FREQUENCY(B2:B17,D2:D5)

and press CTRL + Shift + Enter.

It will return the frequency.

Result:

Example 2:

Suppose you have a list of student IDs who have failed in one or other subjects in your class along with the subjects as shown below.

Now, all those who have failed (whether in one subject or more), they will be considered as “Fail”. Now, you need to know the number of students who have failed.

To identify this, you can use the following syntax:

Syntax: =SUM(–(FREQUENCY(A2:A15,A2:A15)>0))

Result: It will return 8

Let us look at the syntax in detail:

FREQUENCY(A2:A15,A2:A15) will calculate the frequency of data A2:A15 using the interval A2:A15. It will return {2; 2; 2; 1; 2; 2; 2; 0; 0; 1; 0; 0; 0; 0;0}

FREQUENCY(A2:A15,A2:A15) >0 will check if the obtained frequency is greater than zero. It returns logical TRUE if it is greater than zero else FALSE. It will return {TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE ; FALSE; FALSE; TRUE; FALSE; FALSE; FALSE;FALSE; FALSE}

SUM ( — (FREQUENCY (..) > 0) ) will then sum up the TRUE and return the number of unique values.

Example 3:

Suppose you have data of customers visited at a supermarket in a day along with the time of their visit in the cells B2:B25 as shown below.

Now you want to see at which time intervals, the customers visited the most in the store. This will help you plan the employees working hours in an efficient way. The store opens at 11:00 AM and closes at 8:00 PM.

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

• 11:00 AM
• 12:00 AM
• 1:00 PM
• 2:00 PM
• 3:00 PM
• 4:00 PM
• 5:00 PM
• 6:00 PM
• 7:00 PM
• 8:00 PM

Now, select the cells in the frequency table is to be obtained. F2:F11 in this case. Since the store closes at 8:00 PM, we do not select the cell for > 8:00 PM as it will be zero in all cases.

Now, enter the following syntax:

Syntax: =FREQUENCY(B2:B25,E2:E11)

Result:

It will return the frequency of customer visits to the store. In this case, most visits were observed between 5:00 PM – 6:00 PM.

Note:

1. If data_array contains no values, FREQUENCY returns an array of zeros.
2. If bins_array contains no values, FREQUENCY returns the number of elements in data_array.

To create a frequency distribution using FREQUENCY:

• We need to enter numbers that represent the bins we want to group values into.
• Make a selection the same size as the range that contains bins, or greater by one, if we want to include the extra item.
• Enter the FREQUENCY function as an array formula using Control+Shift+Enter.