How to use the MODE.MULT function

What is the MODE.MULT function?

The MODE.MULT function calculates the most frequent number in a cell range. It will return multiple numbers if they are equally frequent. Text and boolean values are ignored.

The MODE.MULT function is entered as an array formula in Excel versions prior to Excel 365. Enter the function as a regular function in Excel 365, it will spill values to cells below if needed.

How to enter the function as an array formula?

1. Double press with left mouse button on the cell where you want to enter the formula.
2. Enter the function, for example: =MODE.MULT(B3:B10)
3. Press and hold CTRL + SHIFT.
4. Press Enter once.
5. Release all keyboard keys.

The formula is now enclosed with curly brackets, like this: {=MODE.MULT(B3:B10)}

Don't enter these characters yourself, they appear automatically.

1. Introduction

What is the mode or modal in statistics?

The mode or modal is the most frequent value in a given data set.

Why calculate the most frequent value (mode or modal)?

It condenses a whole set of data into one value that indicates the center of its distribution. It is useful for describing categorical data such as the most popular ice-cream, company, or group. The mode value may also describe the symmetry of numerical data.

Th most used measures that indicate the center of its distribution are AVERAGE, MEDIAN and MODE or modal. The AVERAGE function calculates the arithmetic mean, the MEDIAN function extracts the middle number, and the MODE.MULT function returns the most repeated number(s) in a distribution.

Other measures that indicate the center of its distribution are the harmonic mean (HARMEAN function) and the geometric mean (GEOMEAN function). They are less common but important to know about.

How can you interpret the mode in relation to the mean and median?

The mode can be interpreted in relation to the mean and median to describe the shape and symmetry of a distribution.

When a group of numbers is symmetrical, the mean, median and mode are equal. They vary if the distribution is skewed.

For a skewed distribution (SKEW function), the mode is located at the peak of the distribution, while the mean is pulled towards the tail of the distribution. The median is usually between the mean and the mode but not always.

This distribution shown in the image above is positively skewed or right-skewed because it has a long right tail. The mode is 1, the median is 4, and the mean is approx. 4.54. The mean is greater than the median and the mode. This is because the mean is influenced by the extreme values in the right tail of the distribution, such as 7, 8, 9 and 10.

What is the difference between the MODE.SNGL, MODE.MULT, and the MODE functions?

The MODE function is a legacy function, it may be removed any time without any notice according to Microsoft. This function is deprecated and should not be used. It is only maintained for backward compatibility.

Both the MODE.SNGL and MODE.MULT functions return the most frequent value, however, the MODE.MULT function returns multiple values if they are equally frequent.

Can the MODE.MULT function extract the most common text value?

No, it ignores text and boolean values. There is a workaround, see section 6 below.

2. MODE.MULT Syntax

MODE.MULT((number1,[number2],...)

3. MODE.MULT Function Arguments

number1	Required. A number or reference for which you want to calculate the MODE for.
[number2]	Optional. Up to 254 additional arguments.

4. MODE.MULT Function Example

The MODE.MULT function demonstrated in the picture above returns both 2 and 4. Both 2 and 4 exist 3 times each in cell range B3:B10. These values are the most frequent values.

Formula in cell D3:

The formula in cell D3 returns 2 numbers, Excel 365 spills values to cells below as far as needed, however, earlier Excel versions require you to enter the formula as an array formula. Instructions on how to enter a formula as an array formula are at the top of this article.

A frequency table shows how many times each value occurs in a given data set. Here is how to create a frequency table in Excel 365:

Back to top

4.1 How to create a frequency table based on numerical values

The formula in cell D4 creates a frequency table based on the data set specified in cell range B3:B10. The output values spills to cells below and to the right as far as needed.

The frequency table shows that numbers 2 and 4 are the most frequent values in cell range B3:B10, they also match the output from the MODE.MULT function.

Excel 365 formula in cell D4:

Remove last row

The FREQUENCY function returns an array larger than the argument array, this creates a #N/A error in the output. Use the DROP function to remove the last row if needed. Here is how:

Sort table by frequency

The output is sorted based on the numbers from small to large, to sort by frequency use this formula:

This sorts the output by the second column from large to small.

Explaining formula in cell D4

The Evaluate Formula tool is located on the Formulas tab in the Ribbon. It is a useful feature that allows you to step through and evaluate complex formulas to understand how the calculation is being performed and identify any errors or issues. The following steps shows these detailed evaluations for the formula above.

Step 1 - Extract unique distinct numbers

The UNIQUE function returns a unique or unique distinct list.

Function syntax: UNIQUE(array,[by_col],[exactly_once])

UNIQUE(B3:B10)

becomes

UNIQUE({4;4;1;2;3;2;4;2})

and returns

{4;1;2;3;}

Step 2 - Sort numbers from small to large

The SORT function sorts values from a cell range or array

Function syntax: SORT(array,[sort_index],[sort_order],[by_col])

SORT(UNIQUE(B3:B10))

becomes

SORT({4;1;2;3;})

and returns

{1;2;3;4}

Step 3 - Calculate frequency

The FREQUENCY function calculates how often values occur within a range of values and then returns a vertical array of numbers.

Function syntax: FREQUENCY(data_array, bins_array)

FREQUENCY(B3:B10,SORT(UNIQUE(B3:B10)))

becomes

FREQUENCY({4;4;1;2;3;2;4;2},{1;2;3;4})

and returns

{1;3;1;3;0}

Step 4 - Stack arrays horizontally

The HSTACK function combines cell ranges or arrays. Joins data to the first blank cell to the right of a cell range or array (horizontal stacking)

Function syntax: HSTACK(array1,[array2],...)

HSTACK(SORT(UNIQUE(B3:B10)),FREQUENCY(B3:B10,SORT(UNIQUE(B3:B10))))

becomes

HSTACK({1;2;3;4},{1;3;1;3;0})

and returns

{1,1;2,3;3,1;4,3;#N/A,0}

Step 5 - Shorten the formula

The LET function lets you name intermediate calculation results which can shorten formulas considerably and improve performance.

Function syntax: LET(name1, name_value1, calculation_or_name2, [name_value2, calculation_or_name3...])

HSTACK(SORT(UNIQUE(B3:B10)),FREQUENCY(B3:B10,SORT(UNIQUE(B3:B10))))

SORT(UNIQUE(B3:B10)) is repeated twice in the formula.

x- SORT(UNIQUE(B3:B10))

LET(x,SORT(UNIQUE(B3:B10)),HSTACK(x,FREQUENCY(B3:B10,x)))

Back to top

5. How can you test if a data set has a unique mode or multiple modes?

The manual way to check if a distribution has a unique mode is to create a frequency table, however, the MODE.MULT returns multiple values if multiple modes exist.

The formula in cell D3 tests if a given distribution has a unique mode or multiple modes. It returns TRUE if the data set has a unique mode and FALSE if multiple modes exist.

Cell range B2:B10 contains example data which are displayed in the image above, the cell range has the following values:

Numbers
4
4
1
2
3
2
4
2

The most frequent value in B3:B10 is 2 and 4 which both exists three times each. The MODE.MULT function returns two values.

Formula in cell D3:

The formula returns FALSE because the B3:B10 has not a unique mode but two modes.

Explaining formula in cell D3

The Evaluate Formula tool is located on the Formulas tab in the Ribbon. It is a useful feature that allows you to step through and evaluate complex formulas to understand how the calculation is being performed and identify any errors or issues. The following steps shows these detailed evaluations for the formula above.

Step 1 - Calculate most frequent value

The MODE.MULT function returns the most frequent number in a cell range. It will return multiple numbers if they are equally frequent.

Function syntax: MODE.MULT((number1,[number2],...)

MODE.MULT(B3:B10)

becomes

MODE.MULT({4;4;1;2;3;2;4;2})

and returns {4;2}.

Step 2 - Count values

The COUNT function counts all numerical values in an argument.

Function syntax: COUNT(value1, [value2], ...)

COUNT(MODE.MULT(B3:B10))

becomes

COUNT({4;2})

and returns 2.

Step 3 - Check if count is equal to 1

The equal sign is a logical operator that lets you test if an expression meets a condition, it returns boolean value TRUE or FALSE.

COUNT(MODE.MULT(B3:B10))=1

becomes

2=1

and returns FALSE.

Back to top

6. Why does the MODE.MULT function return an error?

This example shows that the MODE.MULT function returns an error if the source data contains an error. This means that the MODE.MULT function can't handle errors, you have to find the errors and deal with them.

The IFERROR function lets you ignore error values. Here is an example that solves the problem shown in the image above:

This converts errors to blank values "" which the MODE.MULT function ignores.

How to find errors in a worksheet

The MODE.MULT function also returns an error if there are no duplicate numbers in the source data set.

Back to top

7. Extract the most frequent text values

The formula in cell D3 extracts the most frequent text values from cell range B3:B16. It will return multiple text values if they are equally frequent. Cell range B3:B16 contains the following grades:

Grades

D

E

C

D

A

E

D

B

C

C

B

D

C

B

Excel 365 formula in cell D3:

Cell range B3:B16 contains student grades from A to E, cells D3 and D4 contain grade "D" and "C". They are equally frequent in B3:B16.

Explaining formula

The Evaluate Formula tool is located on the Formulas tab in the Ribbon. It is a useful feature that allows you to step through and evaluate complex formulas to understand how the calculation is being performed and identify any errors or issues. The following steps shows these detailed evaluations for the formula above.

Step 1 - Find relative position in the array

The MATCH function returns the relative position of an item in an array that matches a specified value in a specific order.

Function syntax: MATCH(lookup_value, lookup_array, [match_type])

MATCH(B3:B16,B3:B16,0)

becomes

MATCH({"D";"E";"C";"D";"A";"E";"D";"B";"C";"C";"B";"D";"C";"B"},{"D";"E";"C";"D";"A";"E";"D";"B";"C";"C";"B";"D";"C";"B"},0)

and returns

{1; 2; 3; 1; 5; 2; 1; 8; 3; 3; 8; 1; 3; 8}.

Step 2 - Calculate the mode

The MODE.MULT function returns the most frequent number in a cell range. It will return multiple numbers if they are equally frequent.

Function syntax: MODE.MULT((number1,[number2],...)

MODE.MULT(MATCH(B3:B16,B3:B16,0))

becomes

MODE.MULT({1; 2; 3; 1; 5; 2; 1; 8; 3; 3; 8; 1; 3; 8})

and returns

{1;3}

Step 3 - Find the position of the mode numbers in the array

The MATCH function returns the relative position of an item in an array that matches a specified value in a specific order.

Function syntax: MATCH(lookup_value, lookup_array, [match_type])

MATCH(MODE.MULT(MATCH(B3:B16,B3:B16,0)),MATCH(B3:B16,B3:B16,0),0)

becomes

MATCH({1;3},{1; 2; 3; 1; 5; 2; 1; 8; 3; 3; 8; 1; 3; 8},0)

and returns

{1;3}

Step 4 - Get text values from the corresponding positions

The INDEX function returns a value or reference from a cell range or array, you specify which value based on a row and column number.

Function syntax: INDEX(array, [row_num], [column_num])

INDEX(B3:B16,MATCH(MODE.MULT(MATCH(B3:B16,B3:B16,0)),MATCH(B3:B16,B3:B16,0),0))

becomes

INDEX(B3:B16,{1;3})

and returns

{"D";"C"}.

Step 5 - Shorten formula

The LET function lets you name intermediate calculation results which can shorten formulas considerably and improve performance.

Function syntax: LET(name1, name_value1, calculation_or_name2, [name_value2, calculation_or_name3...])

INDEX(B3:B16,MATCH(MODE.MULT(MATCH(B3:B16,B3:B16,0)),MATCH(B3:B16,B3:B16,0),0))

MATCH(B3:B16,B3:B16,0) is repeated twice.

x - MATCH(B3:B16,B3:B16,0)

B3:B16 is repeated three times.

y - B3:B16

LET(y,B3:B16,x,MATCH(y,y,0),INDEX(y,MATCH(MODE.MULT(x),x,0)))

Back to top

Functions in 'Statistical' category

The MODE.MULT function function is one of 73 functions in the 'Statistical' category.

Excel function categories

Excel categories

Leave a Comment