How to Use Google Sheets Mode Function to Return Most Repeating Value

0
215
mode in spreadsheet

Mode is one of the very simple statistical function in Google Sheets. This time we can see how to use Google Sheets MODE function. It is easy to find the most repeating date or number from a data set using the MODE Google Sheets function.

I will come to the Syntax part later. Before that let me explain you what Google Sheets tells about this function. In their words we can use the MODE function to return the most commonly occurring value in a data set. Here lies the secret. Most commonly occurring value can be date or numeric value as Google Spreadsheet consider date as value.

At this point, I suggest you to go through my ultimate Google Sheets tutorial on Date functions because I am going to use one date function here called to_date. Though I will explain it here in this tutorial, you can any how find it under the first tutorial below.

  1. The complete Google Sheets date functions.
  2. How to return Nth Occurrence of a Day in a Month.

Now let us back to our tutorial on MODE functions. I will tell you how to use MODE function in Google Sheets with few examples.

Syntax:

MODE(value1, [value2, …])

Nothing is important there to explain with the Syntax. You can easily grasp it from the below examples.

How to Use Google Sheets Mode Function to Return Most Repeating Numeric Value

=mode(1,2,2,3,4,4,4,5)

The above MODE formula will return the most occurring number in it, i.e. 4 which repeats 3 times. Hope you understood how to use MODE function in Google Sheets. It’s so simple. No complications like functions DSUM, SUMPRODUCT, QUERY etc.

Now one more example with a range of data.

how to use Google Sheets Mode function with number - example

In the above Mode function example, the most repeating number is 100. So the function will return it.

How to Use Mode Function to Return Most Repeating Date Value

This also I will explain with an example.

Example to Mode function with date

When you apply Mode function to a range of data with date, the result will be a value equal to the date. To covert that value to date, I’ve used to_date function combined with MODE function.

This way you can use MODE function with logical functions too.

For print or eBook edition of our Spreadsheet tutorials, please have a look at our download section.

LEAVE A REPLY

Please enter your comment!
Please enter your name here