How To Create A Frequency Distribution In Google Sheets

This time the topic is Frequency Distribution. You can get frequency distribution with the help of a function called FREQUENCY in Sheets. Learn how to use the Frequency Function in Google Sheets.

Frequency is an Array Function that returns a vertical array result. In Google Sheets, you can use it to count the frequency of values in a range. This range is actually called a one column array.

In Google Sheets, no need to use the function ArrayFormula together with the FREQUENCY formula. Let me explain to you the use of Frequency function in Google Sheets.

Syntax:

FREQUENCY(data, classes)

Below are the details of the Arguments used in this Function.

Data: It is the one column array containing the values to be counted.

Classes: It is the range containing the set of Classes. Here you should take care of one thing. If there are multiple values, you should Sort them. In Data, it's not applicable.

Actually, the Classes is equal to;

=sort(unique(data))

With the below formula examples, you can clearly understand the Frequency function.

Example of the Frequency Function in Google Sheets

The below is an output of a sample survey that showing the number of vehicles available in all the homes in an area.

In the below sample dataset, for frequency calculation, the function arguments are as follows.

Data: Cell reference B2: B16.

Classes: D2: D5.

Example to Frequency Distribution in Google Sheets

The formula in E2 returns the count of each Class. In this example, there are four houses with no vehicles, seven houses with 1 vehicle, three houses with 3 vehicles and one house with 4 vehicles.

To get all Classes in Cell D2 I've applied the below formula. It's the Unique values in the range B2:B16 but in sorted order.

=sort(unique(B2:B16))

See one more example so that you can easily understand how to use the Frequency Function in Google Sheets.

Now I am just putting the value 2 in Cell D2 to use as the Class and changing the Class accordingly in the formula.

=FREQUENCY(B2:B16,D2)

This would return the following output.

Frequency Distribution with Single Class

There are no houses with exact 2 number of vehicles. The formula counts the number of houses with 0 vehicles and one vehicle. That means the number of houses with the count of vehicles "<=2".

That means you can replace the above Frequency formula with a COUNTIF formula as below.

=COUNTIF(B2:B16,"<=2")

Please again refer to the first screenshot. The same formula result you can replicate by using a Query formula as below.

=query(B2:B16,"Select B, Count(B) group by B")

That's all about Google Sheets Frequency function. Enjoy!