Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Mark Neuffer
 
Posts: n/a
Default 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.
  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
vandenberg p
 
Posts: n/a
Default 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.
  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Mark Neuffer
 
Posts: n/a
Default 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.

  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Mark Neuffer
 
Posts: n/a
Default 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.






  #6   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Biff
 
Posts: n/a
Default 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.






  #7   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Jim Cone
 
Posts: n/a
Default 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


  #8   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Harlan Grove
 
Posts: n/a
Default 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.

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
sorting data on protected worksheet Sue Excel Discussion (Misc queries) 20 November 7th 06 08:31 PM
Sort order : Excel vs imported data John Excel Discussion (Misc queries) 0 December 9th 05 02:40 PM
"Group" function very slow with Excel 2003 :( ... While very quick with Excel2000 :O) Alain79 Excel Discussion (Misc queries) 4 June 14th 05 07:34 AM
I cant use englisch function names in a swedich version of excel PE Excel Discussion (Misc queries) 2 December 7th 04 02:00 AM
Data > Sort function amnesia? Dave D Excel Discussion (Misc queries) 1 November 29th 04 11:44 PM


All times are GMT +1. The time now is 04:57 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"