Exceljet

Quick, clean, and to the point

Excel MODE.MULT Function

Excel MODE.MULT function
Summary 

The Excel MODE.MULT function returns an array of the most frequently occurring numbers in a numeric data set. For example, =MODE.MULT(1,2,3,3,4,5,5) returns {3;5}, because there are two modes, 3 and 5.

Purpose 
Get most frequently occurring numbers
Return value 
Numbers representing mode(s)
Syntax 
=MODE.MULT (number1, [number2], ...)
Arguments 
  • number1 - A number or cell reference that refers to numeric values.
  • number2 - [optional] A number or cell reference that refers to numeric values.
Usage notes 

The Excel MODE.MULT function returns a vertical array of the most frequently occurring number(s) in a numeric data set. The mode is the most frequently occuring number in a set of data. When there is just one mode in a set of data, MODE.MULT will return a single result. If there is more than one mode in supplied data, MODE.MULT will return more than one result. If there are no modes, MODE.MULT will return #N/A.

MODE.MULT can accept numbers, ranges, named ranges, or cell references that contain numeric values. Up to 254 individual arguments can be provided. In the example shown, the formula entered in D5:D9 is:

{=MODE.MULT(B5:B14)}

Array formula syntax

The MODE.MULT function returns an array of results and must be entered as an array formula:

  1. Select a vertical range of cells
  2. Enter MODE.MULT function
  3. Confirm with control + shift + enter

In each selected cell, MODE.MULTI will return a mode value, if one exists.

Horizontal array

The MODE.MULT function returns results in a vertical array. To return a horizontal array, add the transpose function:

{=TRANSPOSE(MODE.MULT(range))}

Managing #N/A error

If you are entering the MODE.MULT function in multiple cells to display multiple modes when they exist, you will see #N/A errors in cells where there is no "nth MODE", as seen in cell D9 in the example above. If you want to suppress these errors, you can use the formula below, entered normally and copied down:

=IF(ROWS(exp)<=COUNT(MODE.MULT(rng)),INDEX(MODE.MULT(rng),ROWS(exp),"")

where exp is an expanding range like $A$1:A1, and rng is the range containing numbers. As the formula is copied down, ROWS returns an incrementing counter, which is used to retrieve the "nth mode" when the counter is less than or equal to the number of modes returned by the MODE.MULT function.

Note: I saw this trick demonstrated by the always excellent Mike Girvin on Youtube.

Dynamic Array Excel

In the Dynamic Array version of Excel, MODE.MULT spills the right number of multiple modes automatically with no #N/A errors

Notes

  • If supplied numbers do not contain duplicates, MODE.MULT will return #N/A
  • The MODE function ignores empty cells, and cells that contain boolean values or text.
  • Arguments can be numbers, names, arrays, or references.
  • MODE.MULT was introduced in Excel 2010.