Microsoft Office Tutorials and References
In Depth Information
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.
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 column B, five values each occur twice, creating a five-way tie
for the MODE. Select cells C3:C7, type =MODE.MULT(B3:B12)
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.