How to use FREQUENCY Function in Google Sheets [Complete Guide]

Hello everyone. In this article, we will learn about how to use FREQUENCY function in google sheets with examples. In general, frequency is the number of occurrences of a number, element, or anything inside a process. In math, the frequency can be said as the number of rounds, frequency is the amount of a number that occurred inside an array or list.

Here, in google sheets, FREQUENCY function is used for the frequency distribution of an array, which means we can distribute our values by classes using FREQUENCY function. What are classes? Classes are the segments of an array there can be x number of segments within a data array, and the data is distributed among these classes to get the frequency of each class value. It will be more clarified using some examples later in this article.

In math, we use frequency distribution based on classes, where we could have some (numeric) data, from which first we make classes, then we count each number and add to its corresponding class. This is how we do this manually in traditional math operations. There can be a lot of use cases for the FREQUENCY function in google sheets. We will see some of them below.

Why use FREQUENCY Function in Google Sheets

Many times, we have data where we need to find the segments of the data to simplify it. For this purpose, we make classes inside the data. For example, if the data has 20 numbers in which unique values are 4 so the classes for this data_array will be those 4 unique numbers, then we will find how many times each number is repeated so that they are making a total of 20 numbers. This value will be called the frequency of each number. But, don’t you think it’s tedious work?

Well, we have a FREQUENCY function. We don’t need to do manual work, and also to avoid any kind of error we will use the function method and quickly find out the frequency as well as the classes. Yes, we don’t need to find classes or frequencies manually, we will find out both using functions. So, to work with classes, and frequency distribution we have the FREQUENCY function and we need to learn it to avoid the manual methods and as well as the margin of error.

How to use FREQUENCY Function in Google Sheets

Here, we will see the step-by-step procedure to get a good understanding of this function, and then we will see various examples to make sure you have got the proper understanding and area of utilization of this function.

FREQUENCY Function in Google Sheets – Syntax

Syntax:

FREQUENCY(data, classes)

Here,

1. data: It is a data array to be counted, that is typically a range reference such as A2:A10, it’s a mandatory argument.
2. classes: It is the classes range reference, or you can directly pass a class number such as 4, it is also a mandatory argument

NOTE: The classes should be sorted for clarification, although FREQUENCY Function will sort the values specified internally, and will still give correct results.

FREQUENCY Function in Google Sheets – A Simple Example

There are many possible use cases for the FREQUENCY function in google sheets. To understand for the very first time, I am using a very simple example. I have some employees’ data, and their number of days off in a column. I will create classes from the number of days off. So, Class1 will represent the employees who have 1 day off, Class2 will represent the employees who have 2 days off, and so on. Then using the FREQUENCY function, we will find out the number of employees for each class. Such as 4 employees have 1 day off, 3 employees have 2 days off, and so on.

I hope you understand the use case. Now let’s practically implement this example.

I have a below data set to use in this example.

Step 1

Sample data

Step 2

Create classes from the number of days off, in column C

Step 3

Now to find the frequency of days off for each employee we will use the function, and write the FREQUENCY function in column D.

Step 4

Pass the first argument (The data range)

Step 5

Pass the second argument (The classes)

Step 6

The overall formula for this example is:

Step 7

Press Enter key and you will get the result

Step 8

The sum of frequencies + reminder must be equal to the total data points in your supplied array

Step 9

If the reminder is not equal to zero means that you have not made all the classes (It’s valid, you don’t need to make all the classes, you can make as many as you need)

So, this is how you find the frequency for the distribution of the data using classes.

FREQUENCY Function in Google Sheets – Creating Classes

You may notice in the above example, that creating classes can be tricky, and it can cause some mistakes. Yes, right when you have a bigger data set you could do mistakes very easily while creating the classes. So, in this method, we will show you how to create classes automatically using a function, and we will use a bigger dataset so that you can understand why we need to make auto classes for the frequency distribution. Remember that any mistake in the classes will not give you optimum results for the frequency distribution.

This is a similar dataset where will not see the off-days, instead, we will see the work days of the employees. The workdays have more variation and they can be from anywhere within a month day such as 1-31. So, the number of classes can go higher.

Step 1

Sample data

Step 2

In column C, write the formula to create auto classes

=SORT(UNIQUE(A2:A25))

Step 3

You got the classes

Step 4

Now, similarly, in column D, write the frequency formula

Step 5

Pass the arguments, as you have already learned in the previous section.

Step 6

Press Enter key and you’re done.

Now, I believe you have a better understanding of classes, the classes are nothing but sorted unique values in a data set, and the frequency is the number of each occurrence, This is how it is done. I hope you have how not got a good understanding of how to use the FREQUENCY function in google sheets. Let’s see some more examples.

FREQUENCY Function in Google Sheets – Example 2

In this section, we will learn how to use the FREQUENCY function in google sheets using another example. Suppose you need to find the number of Mobile phones in each house in your area. This example can be easily done using the FREQUENCY Function.

Step 1

The sample data for this example

Step 2

Use =sort and =unique function to drive your classes

Step 3

Use the frequency function to get the frequency of each house

This is how simple it was; I hope you have learned the FREQUENCY function in google sheets.

Now you may be thinking about the COUNTIF function, we can do similar things using COUNTIF, yes, no doubt about it. COUNTIF can also be used to find the number of occurrences of each data point inside a data array

=COUNTIF(L2:L12,”>=2″)

The same thing can also be done using the QUERY function in google sheets. There are many ways of doing the same things. Google sheets is a programmatically developed application that always welcomes custom formulas and logic to perform operations.

=QUERY(L2:L12,”select count(L) where L>2″)

Download/Copy Google Sheets Workbook

Note: Don’t ask for editor access. Make your copy for your practice

Important Notes 

Frequently Asked Questions

Can I Use the PERCENTILE Function in Google Sheets to Calculate Frequency?

Yes, you can use the google sheets percentage function to calculate frequency. The PERCENTILE function in Google Sheets allows you to find the nth percentile of a set of values, which is useful for determining frequency distribution. It’s a valuable tool for analyzing data in spreadsheets.

Can the PI Function be Used in Combination with the FREQUENCY Function in Google Sheets?

The pi function in google sheets can indeed be used in combination with the frequency function. By utilizing the pi function, which returns the value of pi, and the frequency function, which calculates how often values occur within a range, you can analyze data and determine the frequency of pi occurrences in your sheet.

What are classes in frequency function?

Classes are the unique values in a dataset that represents the values that may occur many times in a dataset, a number that has occurred at least once in the dataset can be said as a class in the frequency function. Classes can be made manually by seeing and copying unique values, or you can use the function =SORT(UNIQUE(L2:L25)

Conclusion

Wrapping up how to use FREQUENCY function in google sheets, we have learned how to use this function to solve various frequency distribution problems for an array inside google sheets. We discussed the “classes” which are very important to understand when working with FREQUENCY function. We have seen how to create classes automatically and how to use them inside the frequency function, we also discussed what is reminder and why is it important. That’s all from how to use FREQUENCY function in google sheets.

I hope you find this tutorial helpful. Thank you so much for reading. Keep learning with Office Demy