MODE.SNGL function and MODE.MULT function. Get mode.(Microsoft Excel)
A newer version of the MODE function that retrieves the mode frequency.
Since the MODE function has been deprecated, use the MODE.SNGL and MODE.MULT functions.
How it works
MODE.SNGL
Get a single mode.
If there are multiple mode values, the first value to appear will be the result.
If there is no mode, #N/A error is returned.
=MODE.SNGL(number1 to 255)
| Name | Omission | Description |
|---|---|---|
| number1 to 255 | Required argument. | Specify a list of numbers to get the mode. |
MODE.MULT
Obtains multiple mode values.
=MODE.MULT(number1 to 255)
| Name | Omission | Description |
|---|---|---|
| number1 to 255 | Required argument. | Specify a list of numbers to get the mode. |
For versions where Spill are not available, they must be written in Array formula.
Demonstrate
Use the following data as a sample. The mode values are 2 and 9.
MODE.SNGL
The MODE.SNGL function specifies a list of numbers (primarily a range of cells).
There can be only one mode value in the result.
If there are multiple values, the result will be the first value that appears in the data (top left most).
In this example, 2 and 9 are the most frequent values, but since 9 appears first in "Data", the result will be 9 only.
MODE.MULT
Unlike the MODE.SNGL function, the MODE.MULT function can be applied when there is more than one mode.
If your version supports Spill, you do not need Array formula.
For versions that do not support Spill, the Array formula is required, which makes it less useful.
First, create a function with a data range as the argument, similar to the MODE.SNGL function.
Press [Ctrl]+[Shift]+[ENTER] to confirm the formula and it becomes an Array formula.
Specify other than cell range.
It is also possible to have a function argument that returns a range of cells as a result. In that case, it will be the number of results of the function.
List of functions that result in a range of cells.
| Function | Category | Description |
|---|---|---|
| TEXTSPLIT | Text | Divides text into cells based on a specified character. |
| XLOOKUP | Lookup & Reference | Get data matching the search value. |
| FILTER | Lookup & Reference | Get a list of data that matches the condition. |
| UNIQUE | Lookup & Reference | Delete duplicate data in a specified cell range. |
| SEQUENCE | Lookup & Reference | Create sequential numbers in multiple cells. |
| SORT | Lookup & Reference | Sort a range of cells by a specified column. |
| SORTBY | Lookup & Reference | Sort a cell range by specifying multiple reference columns. |
| DROP | Lookup & Reference | elete a specified number of rows and columns from a specified cell range. |
| TAKE | Lookup & Reference | Obtains a specified number of rows and columns from a specified cell range. |
| TOCOL | Lookup & Reference | Arranges a specified range of cells in a single column. |
| TOROW | Lookup & Reference | Sort a specified range of cells into a single column. |
| EXPAND | Lookup & Reference | Extended specified cell range. |
| CHOOSEROWS | Lookup & Reference | Get the row at the specified position from the specified cell range. |
| CHOOSECOLS | Lookup & Reference | Get the column at the specified position from the specified cell range. |
| WRAPROWS | Lookup & Reference | Sort a range of cells in a single column or row by a specified number of rows. |
| WRAPCOLS | Lookup & Reference | Sort a range of cells in a column or row by a specified number of columns. |
| VSTACK | Lookup & Reference | Merge multiple cell ranges in row direction. |
| HSTACK | Lookup & Reference | Merge multiple cell ranges in column direction. |
| TRANSPOSE | Lookup & Reference | Create a cell range with the rows and columns of the specified cell range swapped. |
The range can also be specified as an array.
=MODE.MULT({10,1,2,5,10,1})
=MODE.SNGL({10,1,2,5,10,1})
Multiple values can also be specified simply by separating them with commas.
=MODE.MULT(10,1,2,5,10,1)
=MODE.SNGL(10,1,2,5,10,1)
---