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

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.

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

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

Biff

"Mark Neuffer" <Mark wrote in message
...
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.

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

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

Mark Neuffer <Mark wrote:
: 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.

Excel "mode" function returns different results based on sort
 

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

"vandenberg p" wrote:

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

Mark Neuffer <Mark wrote:
: 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.

Excel "mode" function returns different results based on sort
 

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" wrote:

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

Biff

"Mark Neuffer" <Mark wrote in message
...
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.

Excel "mode" function returns different results based on sort
 

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

"Mark Neuffer" wrote in message
...
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" wrote:

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

Biff

"Mark Neuffer" <Mark wrote in message
...
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.

Excel "mode" function returns different results based on sort
 

http://support.microsoft.com/contact...rt/?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"
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

Excel "mode" function returns different results based on sort
 

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)=COUNT IF(d,G1)),d))

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

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