Buzz
This article introduces you to the MODE.MULT function - one of the commonly used statistical functions in Excel.
Description: The function returns an array of the most frequently occurring, or repeated, values vertically in a dataset or range of data. Supported from Excel 2010 onwards.
Syntax: MODE.MULT((number1,[number2],...)
In which:
- number1,[number2],...): These represent the values to find the mode, where number1 is a mandatory parameter, and other values are optional and can contain up to 254 numbers.
Note:
- The argument values must be numbers, names, arrays, or references containing numbers.
- The arguments are text or error values that cannot be converted to a number type -> causing the function to return an error.
- If the argument is an array reference containing text or logical values -> these values are ignored, although the value 0 is still counted.
- If the dataset does not contain duplicate data -> the function returns the error value #N/A.
Example:
Find the array containing the most frequently occurring values in the data table below:
- In the cell where you want to calculate, enter the formula: =MODE.MULT(D6:D14)
- Press Enter -> the value obtained is:
- Since the result returned is an array, the function must be entered in array formula form -> Select the range of data containing the array to be returned -> press F2:
- Press the shortcut Ctrl + Shift + Enter -> the array containing the most frequently occurring values is:
Here, since the number of elements in the returned array is less than the number of elements in the selected array, it returns the value #N/A.
- In the case where the number of elements in the returned array matches the expected number of elements in the array -> the returned array does not contain error values.
Above is the instruction and some specific examples when using the MODE.MULT function in Excel.
Wishing you all success!
