FREQUENCY in Excel

FREQUENCY Function in Excel

The FREQUENCY function in Excel helps in calculating the frequency of a data value within a given range of values. In other words, it estimates how many times a data value has occurred amongst a given set of values. It provides a vertical array of numbers that correspond to each value’s frequency within a range. It is a built-in statistical function of Excel.

Syntax

The syntax of FREQUENCY function is stated as follows:

Frequency Formula in Excel

You are free to use this image on your website, templates etc, Please provide us with an attribution linkHow to Provide Attribution?Article Link to be Hyperlinked
For 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_arrayIt 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

FREQUENCY function in Excel returns a frequency distribution of the “data_array” in the “bins_array” intervals. The output is always one more than the number of elements in “bins_array.” The extra element in the returned array corresponds to the count of values higher than the highest element of the “bins_array.” Suppose the “bins_array” contains three elements {2, 4, 6}, the function will return four elements {<2, 2-4, 4-6, >6}.

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.

You can download this FREQUENCY Function Excel Template here – FREQUENCY Function Excel Template

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. 

FREQUENCy Function Illustration 1

We have listed the steps to calculate the frequency as follows:

  1. Club the numbers into the intervals {2, 4, 6} in D3:D5.

    FREQUENCY Function Step 1

  2. To calculate the frequency, first, select four cells E3:E6 and then use the following formula:

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


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

  3. Then press “CTRL+Shift+EnterCTRL+Shift+EnterCtrl-Shift Enter In Excel is a shortcut command that facilitates implementing the array formula in the excel function to execute an intricate computation of the given data. Altogether it transforms a particular data into an array format in excel with multiple data values for this purpose.read more.”
    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.

    FREQUENCy-Function-Illustration-4.jpg

  4. 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).

    FREQUENCy-Function-Illustration-5

  5. The output of the FREQUENCY function is shown in the following image:

    FREQUENCy-Function-Illustration-6

Example #2

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

FREQUENCy Function Example 1

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).
FREQUENCy Function Example 1-1
  • Enter the below-mentioned formula:

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

FREQUENCy Function Example 1-2
  • Press “CTRL+Shift+Enter.”

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

Function Example 1-3

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.

Function Example 2

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

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

Function Example 2-1

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

Function Example 2-2

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.

Function Example 3

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.
Function Example 3-1
  • Now, enter the following formula:

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

  • Press  “CTRL+Shift+Enter.” 
Function Example 3-2

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.

Frequently Asked Questions (FAQs)

What is the FREQUENCY function in Excel?

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.

How to do a frequency distribution using the Excel FREQUENCY function?

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.”

What are the two arguments of the FREQUENCY formula in Excel?

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

 

Recommended Articles

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 –

  • 35+ Courses
  • 120+ Hours
  • Full Lifetime Access
  • Certificate of Completion
LEARN MORE >>
Reader Interactions

Leave a Reply

Your email address will not be published. Required fields are marked *