Close the skill gap with the Financial Modeling & Valuation Analyst (FMVA)® Certification >> Enroll today and save!

MODE.MULT Function

Returns a vertical array of statistical modes within a list of given numbers

What is the MODE.MULT Function?

The MODE.MULT function is categorized under Statistical functions. It will return a vertical array of statistical modes within a list of given numbers. The function was introduced in MS Excel 2010 and hence is unavailable in earlier versions.

As a financial analyst, MODE.MULT is useful in financial analysis, as it helps find out the most frequently occurring numbers in a given dataset.

 

Formula

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

 

The MODE.MULT function uses the following arguments:

  1. Number1 (required argument) – It is the first number for which we want to calculate the mode.
  2. Number2 (optional argument) – Number arguments between 2 to 254 for which we wish to calculate the mode.

We can use a single array or a reference to an array instead of arguments separated by commas. We need to enter the function as an array formula.

 

How to use the MODE.MULT Function in Excel?

To understand the uses of the MODE.MULT function, let’s consider a few examples:

 

Example 1

Suppose we are given the following data:

 

MODE.MULT Function

 

The formula to use is:

 

MODE.MULT Function - Example 1

 

We get the result below:

 

MODE.MULT Function - Example 1a

 

Remember, the MODE.MULT function returns an array of results and must be entered as an array formula. To enter the formula:

  • Select a vertical range of cells.
  • Enter the MODE.MULT function.
  • Confirm with Ctrl + Shift + Enter.
  • In each selected cell, MODE.MULTI will return a mode value if one exists.

 

Example 2

Suppose we are given the following data:

 

MODE.MULT Function - Example 2

 

The MODE.MULT function returns results in a vertical array. To return a horizontal array, add the TRANSPOSE function. The formula to use is:

 

MODE.MULT Function - Example 2a

 

We get the result below:

 

MODE.MULT Function - Example 2b

 

Example 3

The beauty of the MODE.MULT function is that it returns multiple modes if present in the dataset provided. MODE.MULT is different from MODE.SNGL, as the former returns an array of all of the modes, while the latter returns the lowest mode.

Suppose we are given the following data:

 

MODE.MULT Function - Example 3

 

We can find the multiple modes in the data set. Here, the MODE.MULT function will return a vertical array containing the two values. It must be entered as an array formula into the vertical range of cells (D5-D6).

The formula to be used in cells D5 and D6 is:

 

MODE.MULT Function - Example 3a

 

Remember to select both cells while inputting the formula. Else, we will not get accurate results.

We get the result below:

 

MODE.MULT Function - Example 3b

 

Few notes about the MODE.MULT Function 

  1. #N/A! error – Occurs when there are no duplicates in the values provided.
  2. #VALUE! Error – Occurs when the value provided to the function is non-numeric.
  3. Arguments can either be numbers or names, arrays, or references that contain numbers.
  4. If an array or reference argument contains text, logical values, or empty cells, the values are ignored. However, cells with the value zero are included.
  5. Arguments that are error values or text that cannot be translated into numbers cause errors.
  6. If the dataset includes two modes, the MODE.MULT function will return a vertical array containing the two values. It must be entered as an array formula into the vertical range of cells.

 

Click here to download the sample Excel file

 

Additional resources

Thanks for reading CFI’s guide to important Excel functions! By taking the time to learn and master these functions, you’ll significantly speed up your financial analysis. To learn more, check out these additional resources:

  • Excel Functions for Finance
  • Advanced Excel Formulas Course
  • Advanced Excel Formulas You Must Know
  • Excel Shortcuts for PC and Mac

Free Excel Tutorial

To master the art of Excel, check out CFI’s FREE Excel Crash Course, which teaches you how to become an Excel power user.  Learn the most important formulas, functions, and shortcuts to become confident in your financial analysis.

 

Launch CFI’s Free Excel Course now to take your career to the next level and move up the ladder!