How to use the FREQUENCY function

What is the FREQUENCY function?

The FREQUENCY function calculates how often values occur within a range of values (interval) and returns a vertical array of numbers. It returns an array that is one more item larger than the bins_array.

1. Introduction

What is the absolute frequency?

The absolute frequency is the number of times a specific value or data point appears in a given dataset. It represents the total number of occurrences of each distinct value or category within the dataset. Absolute frequency is also known as the raw frequency or frequency count.

Let's consider an example of exam scores: 65, 82, 86, 91, 65, 82, 86, 94.

Absolute Frequency:

Score 65: 2
Score 82: 2
Score 86: 2
Score 91: 1
Score 94: 1

What is the absolute cumulative frequency?

The absolute cumulative frequency is the running total or accumulation of the absolute frequencies up to a particular value or category in a dataset. It is calculated by adding the absolute frequencies of all the values or categories that are less than or equal to the current value or category.

Score 65: absolute cumulative frequency: 2
Score 82: absolute cumulative frequency: 4 (2+2)
Score 86: absolute cumulative frequency: 6 (2+2+2)
Score 91: absolute cumulative frequency: 7 (2+2+2+1
Score 94: absolute cumulative frequency: 8 (2+2+2+1+1)

What is the relative frequency?

The relative frequency, also known as the frequency ratio or proportion, is the fraction or percentage of times a specific value or category occurs in a dataset. It is calculated by dividing the absolute frequency of a particular value or category by the total number of data points in the dataset.

Score 65: 2/8 = 0.25 or 25%
Score 82: 2/8 = 0.25 or 25%
Score 86: 2/8 = 0.25 or 25%
Score 91: 1/8 = 0.125 or 12.5%
Score 94: 1/8 = 0.125 or 12.5%

The total is 100% (25+25+25+12.5+12.5 = 100)

What is the cumulative relative frequency?

The cumulative relative frequency is the running total or accumulation of the relative frequencies up to a particular value or category in a dataset. It is calculated by adding the relative frequencies of all the values or categories that are less than or equal to the current value or category.

Score 65: 2/8 = 0.25 or 25%
Score 82: 4/8 = 0.5 or 50% (25+25)
Score 86: 6/8 = 0.75 or 75% (25+25+25)
Score 91: 7/8 = 0.875 or 87.5% (25+25+25 +12.5)
Score 94: 8/8 = 1 or 100% (25+25+25 +12.5+12.5)

2. FREQUENCY Function Syntax

FREQUENCY(data_array, bins_array)

3. FREQUENCY Function Arguments

data_array	An array or cell range for which you want to determine frequencies.
bins_array	The intervals which you want to group the values in.

The FREQUENCY function returns two or more values in a vertical array, blank cells and text strings are ignored. This means that you can only use the function with numerical values.

4. FREQUENCY function Example 1

You have a dataset of exam scores ranging from 0 to 100. Use the FREQUENCY function to calculate the number of students who scored within different score ranges?

The image above shows the exam scores in cell range B18:F22. Here are the exam scores:

59	50	38	12	37
75	79	29	59	25
80	48	51	70	95
95	98	52	15	39
38	1	21	90	16

Cell range B25:B34 contains the intervals displayed in the histogram chart below the columns. Cell range C25:C34 contains the intervals the FREQUENCY function uses to calculate how often values occur within the specified range of values.

Array formula in cell D25:

The formula in returns an array of values: {1;3;3;4;2;4;1;3;1;3;0} These numbers are the frequency or count based on the intervals specified in cell range C25:C34.

Note that the array is one value larger than the number of cells in C25:C34. The last value in the array corresponds to values larger than the last interval value, in this example 100. 0 (zero) means that there are no values larger than 100.

The numbers in {1;3;3;4;2;4;1;3;1;3;0} correspond the the intervals in C25:C34, for example the first value 1 in the array correspond to the first interval which is 10 meaning values smaller than or equal to 10. Only number 1 in B18:F22 is smaller or equal to 10.

The formula is an array formula and must be entered as an array formula if you use an Excel version earlier than Excel 365. Excel 365 subscribers can enter the formula as a regular formula simply by pressing Enter.

4.1 How to enter an array formula

Skip these steps if you are an Excel 365 subscriber.

1. Select cell range D25:D35
2. Type the formula.
3. Press and hold CTRL + SHIFT keys simultaneously.
4. Press Enter once. Release all keyboard keys.

The formula will now begin and end with a curly bracket, like this: {=array_formula}

They appear automatically, do not enter these characters yourself.

5. FREQUENCY function Example 2

You are a researcher studying a specific phenomenon, and you have collected data that falls into two distinct categories. However, to begin your analysis, you only need to focus on one of the categories for now. Your task is to calculate the frequency distribution for the data points within this single category, ignoring the other category for the time being.

The first category is labeled "A" and the second category "B", calculate the frequency distribution of category "A"?

The data is in cell range B17:C32 shown in the image above. Here is the data:

Item	Value
A	1
B	2
A	2
B	5
A	4
B	7
A	7
B	10
A	1
B	4
A	4
B	9
A	4
B	7
A	7
B	9

The image above shows the FREQUENCY function entered in F17 using an IF function to filter values based on a condition.

Array formula in cell range F17:

The following formula calculates the unique distinct numbers based on category "A". Excel 365 dynamic array formula in cell E17:

This formula spills values to cell E17 and cells below as far as needed. For example, category "B" contains value 9 which category "A" does not have. Value 9 is not present in the list in E17 and cells below, only values in category "A".

The image above shows the distribution in cell E17 and cells below, the corresponding frequency in cell F17 and cells below. Above the distribution is a histogram chart showing the distribution and the frequency.

Back to top

5.1 Explaining the calculation

I recommend using the "Evaluate Formula" feature to learn more about formulas. This tool allows you to examine formula calculations in detail. It also lets you troubleshoot formulas, what is making my formula return an error?

Go to tab "Formulas" on the ribbon and press with left mouse button on the "Evaluate Formula" button. A dialog box appears, press with left mouse button on the "Evaluate" button to move to the next step in formula calculation. Press with left mouse button on "Close" button to dismiss the dialog box.

Step 1 - Filter values

The IF function lets you return a value if a logical expression returns TRUE and another value if FALSE, the logical expression usually returns a boolean value but their equivalents work just fine. FALSE -> 0 (zero), TRUE -> any number.

IF(B17:B32="A",C17:C32,"")

We want to compare the values in cell range B3:B10 with "A" and if they match then return the corresponding value on the same row in cell range C3:C10. IF no match return a blank "".

IF(B17:B32="A",C17:C32,"")

becomes

IF({"A";"B";"A";... ;"B"}="A",{1;2;2;...;9},"")

becomes

IF({TRUE;FALSE;TRUE;...;FALSE},{1;2;2;...;9},"")

and returns this array: {1;"";2;"";4;"";7;"";1;"";4;"";4;"";7;""}.

Step 2 - Frequency

The FREQUENCY function uses the array from the IF function in the first argument, blank values "" are ignored.

FREQUENCY(IF(B3:B10="A",C3:C10,""),E17#)

becomes

FREQUENCY({1;"";2;"";4;"";7;"";1;"";4;"";4;"";7;""},E17#)

becomes

FREQUENCY({1; ""; 2; ""; 4; ""; 7; ""},{1;2;4;7})

and returns {2;1;3;2;0} in cell range F17 and cells below.

Back to top

6. FREQUENCY function and 3D ranges

You are a scientist and have gathered data across multiple worksheets, how do you calculate the frequency distribution based on multiple worksheets (3D ranges)? Your data is in cell range B3:B10 on two worksheets named '3D range' and '3D range1'

This example demonstrates how to use the FREQUENCY function with 3D references. 3D ranges are cell ranges across worksheets meaning a cell reference to a specified number of worksheets in a workbook. Section 6.1 describes how to enter a 3D range.

Array formula in cell D3:

The image above shows the combined frequency distribution in cell range D3:D11 from worksheets '3D range' and '3D range1' based on the numbers specified in cell B3:B10.

This lets you work with data from multiple worksheets using the FREQUENCY function, I will now show you how to enter a 3D range using the FREQUENCY function.

Other Excel functions that can work with 3D ranges are SUM, AVERAGE, AVERAGEA, COUNT, COUNTA, MAX, MAXA, MIN, MINA, PRODUCT, STDEV, STDEVA, STDEVP, STDEVPA, VAR, VARA, VARP, VARPA functions

6.1 How to enter a formula containing 3D ranges

The formula contains a 3-D reference to a range of worksheet names, make sure you rearrange the worksheets so only worksheets you want to include are being used.

1. Select cell range D3:D10.
2. Press with left mouse button on in the formula bar, the prompt appears.
3. Type =FREQUENCY(
4. Press and hold SHIFT key.
5. Select the last worksheet you want to include in the FREQUENCY function.
6. Release SHIFT key.
7. Select cell range B3:B10 with the mouse.
8. Type , (comma)
9. Select cell range B3:B10 with the mouse.
10. Now enter the formula as an array formula (Excel 365 users can skip this step and simply press Enter).
    1. Here are the steps sto enter an array formula.
    2. Press and hold CTRL + SHIFT keys simultaneously.
    3. Press Enter once.
    4. Release all keys.

The formula has now a leading and trailing curly bracket, like this: {=array_formula]

Don't enter these characters yourself, they appear automatically. The image above does not show these characters because I use Excel 365 which uses dynamic array formulas that automatically spill values to cells below.

6.2 How to rearrange worksheets

The image above shows three worksheet names, "3D range", "Sheet7", and "3D range1". We want to move worksheet "Sheet7" so it is not included in the FREQUENCY function.

1. Press and hold with mouse on worksheet name "Sheet7".
2. Drag with mouse to move the worksheet tab to a new location.
3. Release the mouse button to release the worksheet tab.

The image above shows "Sheet7" in a new location, you can now create a 3D reference to worksheets "3D range" and "3D range1".

Back to top

7. Frequency bug?

Can someone explain to me why this happens?

The example above is working.

FREQUENCY function returns {2; 1; 1}. 2 values (0.1 and 0.2) are equal to or less than 0.2. 1 value (0.3) is larger than 0.2 and equal or smaller than 0.3. 1 value (0.4) is larger than 0.3.

The example below is not working as I thought it would.

FREQUENCY function returns {2; 0; 2} and I don't understand why?

The formula in cell B8:B11 returns this array {0.1; 0.2; 0.3; 0.4}, exactly the same values as in B2:B5.

It seems to be the MOD function but why?

Why am I using the MOD function?
To extract the fractional part of a number.

Get workbook

Frequency bug.xlsx

8. Count unique distinct numbers across multiple sheets

I demonstrated in section 6 that the FREQUENCY function is able to calculate the frequency of given numbers across multiple worksheets or 3D ranges. This technique makes it possible to count unique distinct numbers across multiple worksheets as well.

Note that it is only possible with numerical values, not text values or other values which is quite limiting. However, the new VSTACK and UNIQUE functions makes it possible regardless of value types and without any issues. However, you need Excel 365.

The image above demonstrates three different formulas in column E that

• counts unique numbers (section 8.1)
• counts unique distinct numbers (section 8.2)
• counts duplicate numbers (section 8.3)

from multiple sheets in the same workbook.

8.1 Count unique distinct numbers

The following formula counts unique distinct numbers in multiple sheets (3D range):

Formula in E8:

To enter an array formula, type the formula in a cell then press and hold CTRL + SHIFT simultaneously, now press Enter once. Release all keys.

The formula bar now shows the formula with a beginning and ending curly bracket telling you that you entered the formula successfully. Don't enter the curly brackets yourself.

Explaining formula in cell E8

Step 1 - Count numbers

The FREQUENCY function calculates the number of times a number exists in a cell range, it also has the ability to count numbers across multiple worksheets.

The FREQUENCY function returns the count for the corresponding number only once. Example, 3 exists twice in column B above so the function returns 2 on the same row, however, the next time 3 appears in the list the function returns 0 (zero), see row 8. We can use that to count numbers.

FREQUENCY(Sheet1:Sheet3!$B$2:$D$4, Sheet1:Sheet3!$B$2:$D$4)

returns

{1; 9; 7; 0; 0; 0; 0; 0; 0; 0; 8; 0; 0; 1; 0; 0; 0; 0; 0; 0; 0; 0; 1; 0; 0; 0; 0; 0}.

Step 2 - Check if number in array is not equal to 0 (zero)

The less and greater than sign together means not equal to.

FREQUENCY(Sheet1:Sheet3!$B$2:$D$4, Sheet1:Sheet3!$B$2:$D$4)<>0

becomes

{1; 9; 7; ... ; 0}<>0

and returns

{TRUE; TRUE; ... ; FALSE}.

Step 3 - Convert boolean values to numerical equivalents

The SUMPRODUCT function can't sum boolean values so we need to convert TRUE to 1 and FALSE to 0 (zero). There are a few ways to convert them, you can add a zero or multiply with 1 or in this example use double negatives.

--(FREQUENCY(Sheet1:Sheet3!$B$2:$D$4, Sheet1:Sheet3!$B$2:$D$4)<>0)

returns {1; 1; 1; 0; 0; 0; 0; 0; 0; 0; 1; 0; 0; 1; 0; 0; 0; 0; 0; 0; 0; 0; 1; 0; 0; 0; 0; 0}.

Step 4 - Add numbers in array

SUMPRODUCT(--(FREQUENCY(Sheet1:Sheet3!$B$2:$D$4, Sheet1:Sheet3!$B$2:$D$4)<>0))

becomes

SUMPRODUCT({1; 1; 1; 0; 0; 0; 0; 0; 0; 0; 1; 0; 0; 1; 0; 0; 0; 0; 0; 0; 0; 0; 1; 0; 0; 0; 0; 0})

and returns 6.

8.2 Count unique numbers

This formula counts unique numbers in multiple sheets (3D range)

Formula in E10:

8.3 Count duplicate numbers

Formula in E12:

Get excel tutorial file

Count unique and duplicate numerical data entries from multiple sheets.xls
(Excel 97-2003  Workbook *.xls)

9. Excel file

Back to top

'FREQUENCY' function examples

Functions in 'Statistical' category

The FREQUENCY function function is one of 74 functions in the 'Statistical' category.

Excel function categories

Excel categories

Comments (8)