This time, we’ll explore frequency distribution using the FREQUENCY function in Google Sheets. You’ll learn how to utilize this function effectively.
What is Frequency?
Frequency is an array function that returns a vertical array of results. In Google Sheets, you can use it to count the frequency of values in a specified range, which is referred to as a one-column array.
You do not need to combine the FREQUENCY function with the ARRAYFORMULA function in Google Sheets. Let’s dive into the usage of the FREQUENCY function.
FREQUENCY Function: Syntax and Arguments
Syntax:
FREQUENCY(data, classes)Argument Details:
- data: A one-column array containing the values to be counted.
- classes: A range that defines the set of classes. Ensure that if there are multiple values, you sort them. You do not need to sort the data.
The result of the FREQUENCY function will be a vertical range one row larger than the number of classes. The last value in this range represents the count of elements in the data that exceed all class boundaries.
The classes can be defined as follows:
=SORT(UNIQUE(data))Example of the FREQUENCY Function in Google Sheets
Below is an example output from a sample survey showing the number of vehicles available in homes within a specific area.
In this sample dataset, the function arguments for frequency calculation are:
- data: Cell range B2:B16
- classes: Cell range D2:D5
The formula in cell E2 returns the count for each class. In this example, there are four homes with no vehicles, seven homes with one vehicle, three homes with two vehicles, and one home with four vehicles.
To generate all classes in cell D2, I applied the following formula, which retrieves unique values from the range B2:B16 in sorted order:
=SORT(UNIQUE(B2:B16))Another Example for Better Understanding
Now, let’s put the value 2 in cell D2 as the class and adjust the formula accordingly:
=FREQUENCY(B2:B16, D2)This will yield the following output:
There are no houses with exactly two vehicles. The formula counts the number of houses with zero vehicles and one vehicle, effectively counting the number of houses with vehicles less than or equal to two.
You can replace the above FREQUENCY formula with a COUNTIF formula as follows:
=COUNTIF(B2:B16, "<=2")Refer back to the first screenshot for verification. You can achieve the same result using a QUERY formula:
=QUERY(B2:B16, "SELECT B, COUNT(B) GROUP BY B")That’s all about the FREQUENCY function in Google Sheets. Enjoy exploring its capabilities!
Resources
The FREQUENCY function is widely used for identifying winning or losing streaks in Google Sheets. Here are a few tutorials related to that:
Hello there again Prashanth! I am trying to do a similar task on google sheets but for WORD frequency… I tried your formula but it doesn’t seem to work on text strings. I did some digging and I found this here:
=ArrayFormula(QUERY(TRANSPOSE(SPLIT(JOIN(" ";B3:B);" ")&{"";""});"select Col1, count(Col2) group by Col1 order by count(Col2) desc limit 10 label Col1 'Word', count(Col2) 'Frequency'";0))I found this formula at https://stackoverflow.com/questions/14307125/google-docs-spreadsheet-formula-for-most-frequent-keywords
…it seems to get the word FREQUENCY for single words but it is not measuring multiple combinations of words.
Is this possible with a formula in Google sheets? Thank you again so much for your time and Google Sheets WIZARDRY!
Hi, EJ,
I do have a solution. I will post the update soon.
Hi, EJ,
Here is the new related post link.
Get Most Frequent Keywords from Titles in Google Sheets.
I hope this may serve your purpose (finding the frequency of multiple word strings).