How to Use FREQUENCY in Google Sheets in 2020?

by

Frequencies tell us how often different values occur in a dataset, therefore we can easily calculate frequencies in Google Sheets by using the FREQUENCY() function.

FREQUENCY in Google sheets Calculates the frequency distribution of a one-column array into specified classes.

Sample Usage

FREQUENCY(A2:A40,B2:B5)

Syntax

FREQUENCY(data, classes)

data – The array or range containing the values to be counted.

classes – The array or range containing the set of classes.

classes should be sorted for clarity, but FREQUENCY will sort the values specified internally if they are not and return correct results.

Notes

The output of FREQUENCY will be a vertical range of size one greater than classes as the final value is the number of elements in data greater than any of the class boundaries.

Calculating Frequencies in Google Sheets

Suppose we have the following dataset with different values in Google Sheets:

frequency

To calculate the frequency of each individual value (e.g. count how many 12’s occur, how many 13’s occur, etc.) we need to first define the classes in column B. We can easily do this by typing the following formula in cell B2:

=SORT(UNIQUE(A2:A34))

This produces the following results:

Calculate Frequencies in Google Sheets

Next, we can type the following formula into cell C2:

=FREQUENCY(A2:A, B2:B)

This produces the following results:

frequency results

The way to interpret this output is as follows:

  • The value 30 occurs in the original dataset 2 times.
  • The value 40 occurs in the original dataset 7 times.
  • The value 70 occurs in the original dataset 8 times.
  • The value 90 occurs in the original dataset 4 times.
  • The value 50 occurs in the original dataset 2 times.
  • The value 2900 occurs in the original dataset 4 times.
  • The value 110 occurs in the original dataset 1 time.
  • The value 1000 occurs in the original dataset 1 time.
  • The value 1300 occurs in the original dataset 1 time.
  • The value 20 occurs in the original dataset 1 time.

Calculating Relative Frequencies in Google Sheets

Relative Frequency: How often something happens divided by all outcomes.

Example: Your team has won 9 games from a total of 12 games played:

  • the Frequency of winning is 9
  • the Relative Frequency of winning is 9/12 = 75%

Back to our dataset:

Once we have calculated the frequencies of each individual data value, we can then calculate the relative frequencies of each value by typing the following formula into cell D2:

=C2/COUNT($A$2:$A$34)

This formula calculates the relative frequency of the value 30 in the original dataset:

frequency-relative

Once we’ve calculated this relative frequency, we can hover the mouse over the bottom right corner of cell D2 until a small + appears. Double click the + to copy the formula down to the remaining cells:

frequency-relative-percentage

 

We can convert the final numbers of relative frequencies to the percentage:

frequency-relative-percentage-1

And here is what we get as a result:

frequency-relative-percentage-2

The way to interpret this output is as follows:

  • The value of 30 accounts for 0.06 (or 6.06%) of all values in the dataset.
  • The value of 40 accounts for 0.21 (or 21.21%) of all values in the dataset.
  • The value of 70 accounts for 0.24 (or 24.24%) of all values in the dataset.
  • The value of 90 accounts for 0.12 (or 12.12%) of all values in the dataset.
  • The value of 50 accounts for 0.06 (or 6.06%) of all values in the dataset.
  • The value of 260 accounts for 0.06 (or 6.06%) of all values in the dataset.
  • The value of 2900 accounts for 0.12 (or 12.12%) of all values in the dataset.
  • The value of 110 accounts for 0.03 (or 3.03%) of all values in the dataset.
  • The value of 1000 accounts for 0.03 (or 3.03%) of all values in the dataset.
  • The value of 1300 accounts for 0.03 (or 3.03%) of all values in the dataset.
  • The value of 20 accounts for 0.03 (or 3.03%) of all values in the dataset.

In statistics, the frequency of an event is the number of times the observation occurred/recorded in an experiment or study. These frequencies are often graphically represented in histograms.

I hope with this function in Google Sheets, you will be able to calculate FREQUENCY in Google Sheets for your next projects.

For example, you can use this way to Frequency in marketing and reach.

Frequency is the average number of times the advertisement will be presented to the Reached Population. One way to calculate frequency is to divide the number of Impressions by Reach. Another way is to divide GRPs by Reach Percentage.

How did this post help you? Let me know in the comment below.

Cheers