Excel "mode" function returns different results based on sort orde

[Guest]

I am using "mode" in Excel to return the most common numeric value in a
questionnaire field. One domain contains two equally-represented values ("1"
and "2")."Mode" returns a value of "1" if the list is sorted in ascending
order, and returns "2" if the list is unsorted. I can find no detail from
our cretinous friends at Microsoft as to why this might be so. Any insights
would be appreciated.

 

[Biff]

That's just how it works. Returns the first instance, just like a Vlookup,
Match.

Biff

 

[vandenberg p]

Hello:

Apparently if the the number of values are equal (bi-modal) then mode
returns the value for the first value in the array. So if the first
is 1 then it will return one, if the first is 2 it will return 2.
In fact if you try it with a tri-modal it will do the same. Try
1,1,1,2,2,2,3,3,3 In this order it will return 1, if invert the
order it will return 3. If you 2,2,2,1,1,1,3,3,3 it will return
2. If you try 4,1,1,1,2,2,2,3,3,3 it will still return 1, first
modal value is 1. It does not appear to matter whether it is
a column array or a row array.

It appears there should at least be warning in the help file
that multi-modal arrays behave this way.

Pieter Vandenberg

: I am using "mode" in Excel to return the most common numeric value in a
: questionnaire field. One domain contains two equally-represented values ("1"
: and "2")."Mode" returns a value of "1" if the list is sorted in ascending
: order, and returns "2" if the list is unsorted. I can find no detail from
: our cretinous friends at Microsoft as to why this might be so. Any insights
: would be appreciated.

 

[Guest]

Pieter,

Thank you for your reply. I believe you are correct, and agree that a
warning should be supplied regarding multi-modal arrays. I resorted to a
statistics source, which says this: "If two or more values [in the array]
occur with the same frequency, the smaller value is used." Is there any way
to notify Microsoft about this? Thanks...Mark

 

[Guest]

Biff,

Thanks for your reply. Unhappily, I think this treatment of a multi-modal
array is erroneous from a statistical standpoint. Any thoughts about how I
can raise this with Microsoft? Mark

 

[Biff]

Sorry, I have no good recommendation other than to keep an eye on this
thread as there are some really top notch "stats" people in these groups who
may respond.

Biff

 

[Jim Cone]

http://support.microsoft.com/contactussupport/?ws=support
"Contact Us"
http://support.microsoft.com/gp/contactbug
"Report a Microsoft Product Bug...
If you think you have found a bug in a Microsoft product,
contact our Microsoft Product Support Services department.
(800) MICROSOFT (642-7676)"
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware




"Mark Neuffer" <[email protected]>
wrote in message
Pieter,
Thank you for your reply. I believe you are correct, and agree that a
warning should be supplied regarding multi-modal arrays. I resorted to a
statistics source, which says this: "If two or more values [in the array]
occur with the same frequency, the smaller value is used." Is there any way
to notify Microsoft about this? Thanks...Mark

 

[Harlan Grove]

Mark Neuffer wrote...

Thanks for your reply. Unhappily, I think this treatment of a multi-modal
array is erroneous from a statistical standpoint. Any thoughts about how I
can raise this with Microsoft? Mark

....

It's not erroneous. MODE returns a *single* value. If there are
multiple modes, Excel's MODE function returns only one of them, the
first one found. That particular behavior isn't documented in online
help, but it's well known. The value that it does return most
definitely is a mode, so it's hardly a bug that MODE returns different
values depending on the order of the data.

If you want all modes, you need to use multiple MODE calls. For
example, if your data were in a range named d and the first mode found
would be in cell G1 and the next in cell G2, etc, try these formulas.

G1:
=MODE(d)

G2 [array formula]:
=MODE(IF((COUNTIF(G$1:G1,d)=0)*(COUNTIF(d,d)=COUNTIF(d,G1)),d))

Fill G2 down until the formula returns #N/A.

Again, not a bug, just a poorly documented limitation of functionality.