I. Intro
The MODE.MULT function returns an array with the most frequently occurring values in a data set. As it returns an array of values, it must be used together with the ARRAYFORMULA function.
II. About the function
- Formula: =MODE.MULT(value1,[value2,...])
- Parameters:
- value1 (required): The first value or range to be calculated.
- value2 (optional): Additional values or ranges to be calculated.
- Example: =MODE.MULT(A2:A100,5)
- Note:
- If the data set doesn't contain any repeat values, then the function will return the error value #N/A.
- If the array or reference parameter contains text, logical values, or blank cells, these values will be ignored. However, cells containing the value zero will be included in the calculation.
III. Steps
Use the MODE.MULT function
- Select a cell and click Formulas on the toolbar, then select Statistical > MODE.MULT. You can also directly enter =MODE.MULT in a cell.
- Enter the parameters in the cell. For example: =ARRAYFORMULA(MODE.MULT(A2:A11)).
- Press Enter to display the result.
Note: If the formula is entered without the ARRAYFORMULA function, it returns only one mode, which works the same as MODE.SNGL (as shown in the image on the right). To get all the modes, you need to enter it as an array formula (as shown in the image on the left).
250px|700px|reset
250px|700px|reset
Delete the MODE.MULT function
Select the cell with the MODE.MULT function, and press Delete.