MODE in Excel (Formula, Example) | How to Use MODE Function?

MODE in Excel (Table of Contents)

MODE in Excel
MODE Formula
How to Use MODE Function?

MODE Function in Excel

MODE Function is categorized as statistical functions in Excel. In mathematical term, the MODE returns the MODE for a given data sets. The Mode in Excel returns the most often occurring, or repetitive, the value in an array or range of data.

MODE Formula in Excel

Parameters

MODE formula has the following parameters. i.e. number1 and [number2].

number1: it is an array of the number parameters or a set of one or more numeric values for which we want to calculate the mode.
[number2]: it is an array of optional parameters

How to Use MODE Function in Excel?

MODE function in Excel is very simple and easy to use. Let understand the working of MODE Function by some examples. MODE Formula can be used as a worksheet function and as VBA function.

You can download this MODE Function Excel Template here – MODE Function Excel Template

Example #1

Let’s consider a given set of numbers and we have to calculate the MODE formula in excel of them then apply the MODE Formula on the given values as shown in the below table.

=MODE(A3:A22) output will be 15.

Example #2

Let consider a given set of values here there are two logical Values in the given data set. Now apply the MODE Formula here. The MODE Function ignores the logical value and returns the output.

=MODE(C3:C22) and output will be 15.

Example #3

In MODE Formula example, we have a data set with non-numeric values, empty value and zero in the data, MODE function simple ignore the non-numeric value empty value but consider the zero as value and provide the most repetitive value from the given set of data as shown in the below table.

=MODE(E3:E22) and output will be 15.

Example #4

In MODE Formula example, we have a given set of data in the horizontal table and we apply the MODE function to get the most repetitive values from the given set of data.

MODE Function can be Used as a VBA Function

Suppose we have the data sets located in the excel sheet range from A1 to A10, then we can calculate the MODE formula with the given data sets by using the below VBA functions

Sub MODEcal() // start the MODE function scope

Dim x as Range //declare the range x and y

Dim MODE as interger

set x = Range(“A1:A10”) //set data sets to Range x.

MODE = Application.worksheetfunction.mode(x)

MsgBox MODE // print the MODE value in message box.

End sub // End the MODE function

Things to Know about the MODE Function in Excel

Arguments can either be numbers or names, arrays, or references that contain numbers.
If an array or reference argument contains text, logical values, or empty cells, the values are ignored by this function. However, any cells with the value zero are included.
Arguments that are error values or text that cannot be translated into numbers cause errors.
The MODE function through #NUM! Error if there are no duplicates/repetitive values in the supplied data sets hence, there is no mode within the supplied values.

=MODE(A28:A37) as there are no duplicate values in the supplied data set MODE function through #N/A error.

The MODE function through #VALUE! Error when a value that is supplied directly to the MODE is non-numeric and also is not part of an array. Non-numeric functions that are in the array of values are ignored by the MODE function.

=MODE(C28:C37,”hdd”)