The MODE.MULT function returns a vertical array of the most frequently occurring, or repetitive values in an array or range of data. For horizontal arrays, use TRANSPOSE (MODE.MULT (number1, number2…)). This will return more than one result if there are multiple modes. Since this function returns an array of values, it must be entered as an array formula.
Syntax:= MODE.MULT (number1, [number2], …)
The MODE.MULT function syntax has the following arguments:
- Number1 Required. The first number argument for which you want to calculate the mode.
-
Number2, … Optional. Number arguments 2 to 254 for which you want to calculate the mode. You can also use a single array or a reference to an array instead of arguments separated by commas.
Example: Let’s look at some Excel MODE.MULT function examples and explore how to use the MODE.MULT function as a worksheet function in Microsoft Excel:
Syntax: =MODE.MULT(A2:A17)
Result:
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.
Note:
- #N/A! error – Occurs when there are no duplicates in the values provided.
- #VALUE! Error – Occurs when the value provided to the function is non-numeric.
- Arguments can either be numbers or names, arrays, or references that contain numbers.
- 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.
- Arguments that are error values or text that cannot be translated into numbers cause errors.
- 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.
