How to Use the Excel MODE.MULT Function to Find Multiple Averages

When analyzing a dataset to find the most frequently occurring value—the mode—users typically rely on the standard MODE.SNGL function (or the legacy MODE function). However, real-world data is rarely perfectly uniform. In retail sales data or survey results, you will frequently encounter datasets where two or more numbers tie for the highest frequency. This is known as a multimodal dataset. The standard mode function completely ignores ties, reporting only the very first mode it encounters and discarding critical statistical information. To accurately capture every tie, you must use the MODE.MULT function.

What Does the MODE.MULT Function Do?

The MODE.MULT function evaluates an array of numbers and returns a vertical array of the most frequently occurring, repetitive values. If three different numbers all appear exactly five times in your dataset, the function will return all three numbers simultaneously.

Understanding the Syntax

The syntax is straightforward, requiring a single array or range of numbers, with the option to include additional ranges.

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

• number1: The primary range of data you want to evaluate (e.g., A1:A50).

How to Execute an Array Formula

Because MODE.MULT returns multiple answers rather than a single number, it operates differently depending on which version of Excel you are using.

For Excel 365 and Excel 2021 (Dynamic Arrays):
Modern versions of Excel support dynamic arrays. You simply type the formula into a single cell, and the multiple answers will automatically “spill” downward into the empty cells below it.

1. Assume your data is in cells A1 through A20.
2. Click into cell C1 (ensure the cells below it are empty).
3. Type =MODE.MULT(A1:A20) and press Enter.
4. If the dataset has three modes, the answers will populate cells C1, C2, and C3 automatically.

For Excel 2019 and Older (Legacy Arrays):
Older versions of Excel require you to manually pre-select the output range and execute the formula using a special keyboard shortcut.

1. Highlight a vertical block of empty cells where you want the answers to appear (e.g., C1 through C5).
2. While the cells are highlighted, click into the formula bar at the top of the screen.
3. Type =MODE.MULT(A1:A20).
4. Do not press Enter. Instead, hold down Ctrl + Shift + Enter simultaneously.
5. Excel will wrap the formula in curly braces {} and populate the highlighted cells with the multiple modes. If there are fewer modes than the cells you highlighted, the remaining cells will display an #N/A error.

Handling Datasets Without a Mode

It is important to note that if your dataset contains absolutely no duplicate values (every number appears exactly once), the MODE.MULT function cannot calculate an average and will return an #N/A error. To prevent this error from breaking subsequent calculations on your dashboard, you can wrap the formula in an error handler:

=IFERROR(MODE.MULT(A1:A20), "No Mode Found")

This ensures your spreadsheet remains clean and professional, even when the underlying data lacks a statistical mode.