Excel - MODE Function

---

---

MODE Function

The Excel MODE function calculates the mode of an integer range of the dataset. This function returns the smallest number occurring seamlessly in multiple data points either in the array or specific range of the dataset. For instance: if the dataset contains three or more often occurring field values, then the Mode function returns only the smallest field values which are repeated multiple times.

The MODE function has been replaced with MODE.MULT function and MODE.SNGL function in the latest versions of Excel but although this function is continuing for backward compatibility. The central tendency is determined by the Excel Mode function. The other terms related to calculating the central tendency are Average and Median.

Compatibility

The MODE function is available in the following versions of Excel −

• Excel for Microsoft 365
• Excel for Microsoft 365 for Mac
• Excel for the web Excel 2021
• Excel 2021 for Mac
• Excel 2019
• Excel 2019 for Mac
• Excel 2016
• Excel 2013

Syntax

The syntax of MODE function is as follows −

MODE (number1,[number2],...)

Arguments

You can use the following arguments with the MODE function −

Points to Remember

• Arguments can be either integers or named ranges, arrays, or cell references which refer to numeric values.
• If the defined arguments or array contains a string, Boolean values, or empty cells then these field values are not countable. Only integer values are evaluated.
• Arguments that contain only boolean values, string, or error values lead to errors.
• If the data set contains no duplicate data points, the MODE function returns the #N/A error value.

Examples of MODE Function

Practice the following examples to learn the use of the MODE function in Excel.

Example 1

Arguments can be either integers or named ranges, arrays, or cell references which refer to numeric values.

Solution

You can enter the formula =MODE(C3:C10) in the C11 to find repetitive values in this range. The resulting value is 10 as it is the most occurring number.

Here, you can notice in the screenshot, that 43 is the repetitive number in the C column.

Similarly, enter the formula "=Mode(D3:D10)" in the D11 cell.

Example 2

If the defined arguments or array contains a string, Boolean values, or empty cells, then these field values are not countable. Only numeric values are countable.

Solution

Consider the sample dataset where the D column comprises the mixed set of field values like Boolean values, text, and numeric values. Here, the Mode function is employed only for integer values.

Example 3

Arguments that contain only Boolean values, string, or error values lead to errors.

Solution

You can enter the expression "=MODE(A8, A9)" in the B4 cell. Here, the A8 and A9 cells point to Boolean values. Therefore, a #N/A error will arise while evaluating this expression.

Furthermore, you can enter the formula =MODE("Prithvi", 5) in the B5 cell. Therefore, the output for this expression is #N/A error.

Furthermore, in the B6 cell, enter the formula "=MODE(A5,A6,A7,A8)". Here, the A5 cell contains an empty value. Therefore, the output of this expression is a "#VALUE!" error.

Moreover, the output of the expression =MODE(A4,A6,A7,A10) defined in the B17 cell is #DIV/0!. Here, the A10 cell reference contains an error value that would not convert into a number.

Now, enter the expression =MODE(A5, A8) in the B7 cell and press Enter.

Here, you can see that A5 refers to the text value "Smriti" and A8 refers to the logical value. Therefore, the #N/A error value will be obtained.

Example 4

If the data set contains no duplicate data points, MODE function returns the #N/A error value.

Solution

Write the formula "=MODE(A4:A10)" in the B9 cell and press the Enter.