Return the most common value in a data set
=MODE (number1, [number2], ...)
MODE function is available for backward compatibility only and may not be available in future versions of Excel. It is recommended to use MODE.SNGL or MODE.MULT instead.
Return the most common (frequently occurring, repetitive) value in a numeric data set using either defined values (e.g. 1,1,2,3) or ranges, named ranges or cell references (e.g. C3:C6).
MODE syntax requires the number arguments to be considered.
MODE with a number.
In the example below, the MODE function is used to identify the most frequently occurring numbers in a list of defined values or a cell range.
Use the MODE function to return the most common value in a data set.
Syntax
=MODE (number1, [number2], ...)
The MODE function consists of at least 1 argument:
- number 1. Required. A number or cell reference that refers to numeric values.
- number 2. Optional. A number or cell reference that refers to numeric values.
Try it now!
- Enter your formulas into the grey cells.
Scope
MODE can be applied to numbers, ranges, named ranges or cell references. Up to 255 numbers can be supplied as separate arguments.
|
=MODE(1,2,3,1) || result is 1 |
|
=MODE(C3:C10) where the range holds values in each cell |
Caveats
- MODE will return #N/A if there are no duplicate values to be found
- Empty cells, and cells that contain boolean values or text will be ignored by the MODE function. Cells with the value zero (0) will be included.
Related Functions
- AVERAGE() returns the average (arithmetic mean) of the values in a data set.
- MEDIAN() returns the median (middle number) of the values in a data set.
- MODE.MULT() returns the most common values in a data set as an array.
- MODE.SNGL() returns the most common value in a data set.
Functions Category