MODE function is wrong

  • Thread starter Thread starter michael ellis
  • Start date Start date
M

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
 
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.
 
...
...
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.
Click to expand...

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.
 
Back
Top