Exceljet

Quick, clean, and to the point

Excel MODE.MULT Function

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 individual 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:

  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))}

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.

Excel Formula Training

Formulas are the key to getting things done in Excel. In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Instant access. See details here.