MODE.MULT function

The MODE.MULT function in Excel is used to return the most frequent number (mode) in a dataset. It can return multiple modes if there are more than one. This function is useful when you have a dataset with multiple values that appear most frequently.

Syntax:

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

Arguments:

• number1: Required. The first number, cell reference, or range of values to evaluate.
• number2, …: Optional. Additional numbers, cell references, or ranges to evaluate. You can include up to 255 arguments.

Output:

• MODE.MULT returns an array of the most frequent values in the dataset. If there is more than one mode, it will return all of them. If there’s only one mode, it returns that value.
• If no number repeats (i.e., there’s no mode), it will return an empty array.

How It Works:

• MODE.MULT finds the most frequent value(s) in the dataset. If more than one number has the highest frequency, it will return each of those numbers.
• The result is an array, so you need to enter the formula as an array formula if you expect multiple modes. In Excel, you can press Ctrl + Shift + Enter to enter the formula as an array.

Example:

1. Example 1: Finding the Mode of a Single Set of Numbers Suppose you have the following dataset in cells A1 to A6:

   A1: 5
   A2: 3
   A3: 7
   A4: 5
   A5: 3
   A6: 2
   

   Using the formula:

   =MODE.MULT(A1:A6)
   

   This will return 5 and 3 as both 5 and 3 appear twice, making them the modes of the dataset.

2. Example 2: Handling a Single Mode Suppose you have the following numbers in cells A1 to A5:

   A1: 10
   A2: 20
   A3: 30
   A4: 40
   A5: 50
   

   Using the formula:

   =MODE.MULT(A1:A5)
   

   Since all the numbers are unique, the formula will return an empty array, indicating there is no mode.

3. Example 3: Entering the Formula as an Array If you expect multiple modes (for instance, the dataset in cells A1 to A6 is:

   A1: 5
   A2: 3
   A3: 5
   A4: 3
   A5: 2
   A6: 1
   

   After entering the formula:

   =MODE.MULT(A1:A6)
   

   You need to press Ctrl + Shift + Enter to get the result as an array:

   5  3
   

   This indicates both 5 and 3 are modes of the dataset.

Key Points:

• MODE.MULT is an array function, meaning it can return multiple values if there is more than one mode.
• If there are no duplicates in the dataset, MODE.MULT will return an empty array.
• Unlike MODE.SNGL, which only returns one mode (the first one it finds), MODE.MULT returns all modes when there are multiple.

Use Cases:

• Statistical analysis: Identify the most common values in datasets like survey results, exam scores, or product sales.
• Market research: Find the most popular product or response in a group.
• Education: Analyze student scores to see which grade or score is most frequent.

Notes:

• MODE.MULT is available in Excel 2010 and later.
• If you need a function that returns only one mode (the first mode found), you can use MODE.SNGL.