Microsoft Office Tutorials and References

In Depth Information

**Syntax**

There are also two modes in the table. Both 88 and 82 appear three times.

MODE.SNGL reports 88 as the mode because it encounters the 88 in B8 before

it encounters the 82 in B9. This is rather arbitrary, and MODE.SNGL would

change if the data were sorted in ascending sequence.

Read on to see how MODE.MULT can report all the mode values.

Syntax

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

The MODE.MULT function returns a vertical array of the most frequently

occurring, or repetitive, value in an array or a range of data. MODE.MULT

was introduced in Excel 2010 to specifically address the situations where

two or more values tie as the mode. The MODE.MULT function returns a ver-

tical array of values as the answer.

Because MODE.MULT can return multiple values, you might think that you

should enter the function in several cells and use Ctrl+Shift+Enter to enter

the formula. Although this works, the unpredictability of the number of

values returned by MODE.MULT makes this a dicey proposition.

In
Figure 14.5
,
you see four different cases with MODE.MULT:

•
In column B, five values each occur twice, creating a five-way tie

for the MODE. Select cells C3:C7, type =MODE.MULT(B3:B12)

=MODE.MULT(B3:B12), and

hold down Ctrl+Shift while pressing Enter. This enters one formula in

those five cells. This works out great; five values are returned, and

they fill the five cells where the formula is entered.

•
The first case has been copied to columns E:F. Cell E12 is changed

from 5 to 6. This creates a four-way tie for the MODE.MULT. Because

the formula is entered in five cells, you get the four-way tie as the

first four cells and then #N/A as the fifth cell. This makes sense.

There are a number of ways to deal with the #N/A value.

•
In column H, all 10 values appear exactly once. Excel help warns

that if no value appears two or more times, the answer for mode is

#N/A. The results are all #N/A because there is officially no mode.

•
In column K, the
“
normal
”
case of having one mode causes all

sorts of problems. Because MODE.MULT returns a one-cell answer,

the array formula assumes that you must want to expand that one-

cell answer over the entire range where the formula is entered, so you

get five 9s as the answer.