Excel MODE.MULT Function

Summary

The Excel MODE.MULT function returns a vertical 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}.

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. Numbers can be supplied as numbers, ranges, named ranges, or cell references that contain numeric values. The MODE.MULT function will accept up to 254 inpidual arguments.

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:

Select a vertical range of cells
Enter MODE.MULT function
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))}

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.SNGL was introduced in Excel 2010.