Excel Functions Tutorials

- Excel Formulas Cheatsheet
- Excel
- Financial Functions in Excel
- Logical Functions in Excel
- TEXT Functions in Excel
- Lookup Reference in Excel
- Address Function in Excel
- Choose Function in Excel
- Column Function in Excel
- Columns Function in Excel
- REPLACE Function in Excel
- GetPivotData in Excel
- HLOOKUP in Excel
- Hyperlink Excel Function
- INDIRECT Function in Excel
- LOOKUP Excel Function
- Match Excel Function
- VLOOKUP Excel Function
- INDEX Excel Function
- VLOOKUP vs HLOOKUP

- Maths Functions in Excel
- POWER Function in Excel
- EVEN Function in Excel
- ODD Function in Excel
- ABS Function in Excel
- SUM Function in Excel
- SUMPRODUCT Function in Excel
- SUBTOTAL Excel Function
- ROUND in Excel
- AGGREGATE Excel Function
- PRODUCT Excel Function
- RAND Excel Function
- LOG Excel Function
- EXPONENTIAL Excel Function
- SUMIF in Excel
- TAN Excel Function
- CEILING Excel Function
- LN Excel Function
- SIGN Excel Function
- COS Excel Function
- FLOOR Function in Excel
- SIN Excel Function

- Date and Time Function in Excel
- Statistical Function in Excel

**FREQUENCY on Excel (Table of Contents)**

- FREQUENCY in Excel
- FREQUENCY Formula in Excel
- FREQUENCY in Excel – Illustration
- How to Use FREQUENCY Function in Excel?

## FREQUENCY Function in Excel

The FREQUENCY function in Excel calculates the number of times data values occur within a given range of values. It returns a vertical array of numbers corresponding to the frequency of each value in a range. It is a built-in function in Excel and is categorized as a Statistical Function.

### FREQUENCY Formula in Excel

Below is FREQUENCY Formula in Excel.

**Arguments used for FREQUENCY Formula in Excel.**

**Data_array**Required. An array of or reference to a set of values for which the frequencies is to be counted.**Bins_array**Required. An array of or reference to intervals into which the values in*data_array*are to be grouped.

### Explanation of FREQUENCY Function in Excel

The frequency returns an array of values and thus, it must be entered as an array formula i.e., press CTRL + Shift + Enter (or Command + Shift + Enter for Mac). The cells into which the output is required, those cells are supposed to be selected first and then, the FREQUENCY formula in excel is typed in after which it is entered as an array formula.

Select cells à Type Formula à Press CTRL + Shift + Enter

**Returns**

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 Excel FREQUENCY function returns an array of zeros. If *bins_array* contains no values, the Excel FREQUENCY function returns the total number of elements given in *data_array*.

FREQUENCY in excel is a widely used function in statistics. Sometimes it is required to understand the frequency distribution of a given data rather than just the data. For example, the age of individuals in a population varies to a great extent and thus are visualized in the form of frequencies. Similarly, the marks obtained by each student in a class are clubbed in terms of frequencies to understand the overall performance of the class.

### FREQUENCY in Excel – Illustration

Suppose you have some numbers for which you want to calculate the frequency. The numbers {1, 3, 2, 4, 6, 2, 3, 4, 5} are given in B3:B11.

The numbers are to be clubbed into the intervals : {2, 4, 6} given in D3:D5.

To calculate the frequency, first select four cells E3:E6 and then the following syntax:

= FREQUENCY (B3:B11, D3:B5)

and press CTRL + Shift + Enter.

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

It will return the frequency.

The given output {3, 4, 2, 0} corresponds to the interval {< 2, 2-4, 4-6, > 6}.

If you select only three cells instead of four, the count of “greater than 6” will be omitted as shown below.

**How to Use FREQUENCY Function in Excel?**

Excel FREQUENCY function is very simple and easy to use. Let understand the working of FREQUENCY in excel by some examples.

### FREQUENCY in 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 E4:E7.

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

Then, enter the following syntax:

= FREQUENCY (B4:B14, E4:E7)

and press CTRL + Shift + Enter.

It will return the frequency.

### FREQUENCY in Excel 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:

= SUM ( — (FREQUENCY (B4:B9, B4:B9) > 0) )

It will return 4.

Let us look at the syntax in detail:

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

FREQUENCY (B4:B9, B4:B9) > 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; FALSE; TRUE; FALSE; FALSE}

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

### FREQUENCY in Excel 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 B4:C20 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. G4:G13 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:

= FREQUENCY (B4:C39, G4:G13)

and press CTRL + Shift + Enter.

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

**Things to Remember about FREQUENCY Function in Excel**

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

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

### Recommended Articles

This has been a guide to FREQUENCY Function in Excel. Here we discuss the FREQUENCY Formula in excel and how to use FREQUENCY in Excel along with excel example and downloadable excel templates. You may also look at these useful functions in excel –

## Leave a Reply