Most of the time we come across information or data in the form of numerical values, for example, marks obtained by the students in maths tests, the number of employees in different organizations, or temperatures in different areas. We can easily calculate the frequency of any event in given data if the data is not large enough. If the data is much larger, then there is a greater chance of getting errors in our calculations.
Google Sheets has a built-in FREQUENCY() function that helps us calculate the frequency of the number or event in large-size data without any error. In this guide, we will learn how to use the frequency function in Google Sheets step by step.
How to Use the FREQUENCY() Function in Google Sheets?
The FREQUENCY() function is a statistical function in Google Sheets used to calculate the frequency distribution of events in a large size of data. It uses two arguments, the range of data and the array of classes in the given data.
The following is the syntax used for the FREQUENCY() function in Google Sheets:
FREQUENCY(data, classes)Where,
| Argument | Use |
| data | Number arrays to be counted |
| classes | Range of the reference data |
To better understand the use of the FREQUENCY() function, consider an example of the data of the marks obtained by the students in the maths test and follow the steps below to calculate the frequency of events in the data.
Step 1: Type the given data randomly in the Google Sheets as follows:
Step 2: Column B gives us the data to be calculated, and there are five values repeated randomly in the given data. We call them the Classes in the given data, 12, 14, 15, 17, and 20. We can find the classes by using formulas to avoid any mistakes. For classes, the syntax in Google Sheets is:
SORT(UNIQUE(range of data))Here,
The SORT function is used to arrange the values in a regular pattern.
The UNIQUE function will give you all the unique values that are repeated in the given data.
For our example, we will use the following function to find out classes:
SORT(UNIQUE(range of data))Here,
B2 and B12 are the starting and ending ranges of the data, on which we want to find out the classes. Including B2 and B12, all the rows between them will be selected:
Step 3: After carefully typing all the arguments, press Enter. You will get all the unique values or events from the selected range of data:
Step 4: Now the next step is to calculate the frequencies of the classes. For this, we will use the FREQUENCY() function as follows:
FREQUENCY(B2:B12, D2:D6)Here,
B2:B12 is the minimum and maximum range of the data, on which we want to calculate the frequency of the classes.
D2:D6 are the minimum and maximum range of the classes:
Step 5: Press Enter after typing the data and classes range in the FREQUENCY() function. We will get in return the frequencies of the 12, 14, 15, 17, and 20 in front of them as 1, 2, 3, 1, and 4 respectively:
How FREQUENCY() Function Works in Google Sheets
The FREQUENCY() function in Google Sheets counts the frequency of all the events equal and nearly equal to the highest value of the class. To better understand, consider that we want to calculate the frequency of 15 in the above example, and then the FREQUENCY() function will calculate the frequency of all the values that are less than and equal to 15 as well as the number of values greater than 15:
Now take another example to better understand the working of the FREQUENCY() function in Google Sheets. We take two classes 14 and 17 on which we will apply the FREQUENCY() function.
We will get in return the frequency of all the values that are less than and equal to 14 and the frequency of all the values between 14 and 17 as well as the remaining number of values that are greater than 17:
FREQUENCY() Vs COUNTIF() Function
The COUNTIF() function in Google Sheets does the same job as the FREQUENCY() function. The difference is that the COUNTIF() function does not require a class column. Consider the above example to understand that if we want to count the number of students who got 15 and less than 15 marks in the maths test, then we use the following syntax for the COUNTIF() function in our example:
COUNTIF(B2:B12, "<=15")Now press Enter, and you will see that the COUNTIF() function gives the same values as the FREQUENCY() function in the above example:
Similarly, if we use the COUNTIF() function to calculate the number of students who got marks less than or equal to 20 we will get:
FREQUENCY() Vs QUERY Function
The QUERY() function in Google Sheets is also replaced by the FREQUENCY() function, both of the functions do the same job. However, the FREQUENCY() function is easy to apply as compared to the QUERY() function.
In the above example, if we want to calculate the number of students who obtained marks less than and equal to 15, then we will use the following syntax for the QUERY() function in our example:
QUERY(B2:B12, select B where B<=15)Where,
B2:B12 = The range of the data or cells on which we want to apply the QUERY() function.
select = This argument is compulsory to select the column from which the QUERY() function calculates the number of events or values. In our example, we have selected column B.
where = This is used to give instructions based on which you will get the calculations in return.
In the above example, we use where B<=15 which means that we want to calculate the number of events or values that are less than 15 in column B:
Now press Enter, and you will see that the QUERY() function gives the calculations in return as the FREQUENCY() function given in the above section:
Conclusion
In Google Sheets, the FREQUENCY() function is used to count how many times an event is repeated in a given data. The FREQUENCY() function counts the events or values in a given data less than and equal to the maximum value of the classes. If there is more than one class, then first it counts the values less than and equal to the first class and then counts values between the first and second class, and then so on. The COUNTIF and QUERY() functions worked similarly to the FREQUENCY() function, but the FREQUENCY() function is much easier to apply.