The `MODE.MULT` function in Excel is used to find the most frequently occurring numbers (modes) in a dataset. Unlike the `MODE.SNGL` function, which returns a single mode, `MODE.MULT` can return multiple modes if the dataset is multimodal (i.e., has more than one mode).
Here’s how you can use the `MODE.MULT` function:
Syntax
MODE.MULT(number1, [number2], ...)
- number1, [number2], …: These arguments are the numbers you want to find the mode(s) for. You can input them directly as numbers, as cell references, or as a range of cells.
Important Points
- `MODE.MULT` must be entered as an array formula if you expect to get multiple results.
- To enter an array formula, you must press `Ctrl + Shift + Enter` instead of just `Enter`.
- Excel will automatically display multiple modes in consecutive cells, so you should pre-select the desired number of cells where the modes will appear.
Step-by-Step Guide
- Enter the Data: Assume your data is in the range A1:A10.
- Select Output Cells: Click and drag to select the range of cells where you want to see the result. For instance, select B1:B3 if you expect up to three modes.
- Enter the Formula: Type `=MODE.MULT(A1:A10)` in the formula bar while making sure the cells you want to populate are still selected.
- Complete the Array Formula: Instead of pressing just `Enter`, press `Ctrl + Shift + Enter` to make it an array formula. Excel will fill in the selected cells with the modes.
- Check the Curly Brackets: After entering the formula, Excel will display it with curly braces, like `{=MODE.MULT(A1:A10)}`, indicating it’s an array formula.
Example
Given the data range A1:A10: {1, 2, 2, 3, 4, 4, 5, 5, 5, 6}
- Select cells B1:B2 (assuming you think there might be up to 2 modes).
- Enter the formula `=MODE.MULT(A1:A10)`.
- Press `Ctrl + Shift + Enter`.
- If your data is multimodal (as in this example, where the modes are 2 and 5), Excel will display these values in cells B1 and B2.
Notes
- If only one mode exists, or if the array is not entered with Ctrl + Shift + Enter, not completing the array process may lead to incorrect or incomplete results.
- This function is available in Excel 2010 and later versions.
Using the `MODE.MULT` function helps analyze datasets where you need to identify more than one mode, providing deeper insights into your data’s distribution.
Unlock Your Potential
Excel
Access
Power BI
