MODE function is wrong

[michael ellis]

Hello!

In Excel, mode(3,4,3,4) (or any other set of numbers with
two modes, even with other values in there) returns the
first mode in the list (e.g., 3 in this case).

It should return 3.5, the average of the two modes or both
modes separately.

Anyone know how to patch this?

Thanks,
Michael

 

[J.E. McGimpsey]

The mode is certainly NOT 3.5. Since the mode, by definition, is the
value which occurs most frequently in a data set, how can a value
NOT in the data set be the mode?

As you point out, your data set of (3,4,3,4) is bimodal. 3 is a
mode. 4 is a mode. 3.5 is not.

Unfortunately, XL doesn't have a native function for returning an
array of modes in multi-modal sets.

A UDF would probably be the best bet.

 

[Harlan Grove]

...
...

Unfortunately, XL doesn't have a native function for returning an
array of modes in multi-modal sets.

A UDF would probably be the best bet.

Not necessary fi the data is in a range (named X for my convenience).

=AVERAGE(IF(ISNUMBER(X)*(COUNTIF(X,X)=MAX(COUNTIF(X,X))),X))

returns the average of multiple modes. The array

IF(ISNUMBER(X)*(COUNTIF(X,X)=MAX(COUNTIF(X,X))),X)

contains the modes and FALSE in place of other values. It could be filtered for
only the mode values, e.g., using the following array formulas

B1:
=MIN(IF(ISNUMBER(X)*(COUNTIF(X,X)=MAX(COUNTIF(X,X))),X))

B2:
=MIN(IF(ISNUMBER(X)*(COUNTIF(X,X)=MAX(COUNTIF(X,X)))*(COUNTIF(B$1:B1,X)=0),X))

and fill B2 down as far as needed. If the values could be in any arbitrary
array, udfs would be handier.