Problem using the mode function

Question

Hello,

I'm trying to figure out how to use the MODE function in Excel 2007. Basically it just returns from an array the value most frequently seen or repetitive. That's pretty straight forward. Afer testing it i get confused. Here's my data you can just key it in and then using the MODE function you will notice 16 is the result meaning 16 appears the most. This is correct but ... so is 7 and 20 which are correct answers too. Is there some sort of hierarchy being observed behind the scenes somewhere?  

16,7,7,16,20,16,0,7,20,20,20,7,16

Why did it pick 16 and not 7 or 20 instead as the result?

My observation: If I had to look at a listing of 25000 rows with various values and the results point there were 2 or more sets of values (seen most frequently) that tie, like the example above, how will I know which tied-results got left out even if MODE returned an answer? Is this a reliable function?

Thanks.

Answer 1

Magnus wrote:

Here's my data you can just key it in and then using the MODE function you will notice 16 is the result meaning 16 appears the most. This is correct but ... so is 7 and 20 which are correct answers too. Is there some sort of hierarchy being observed behind the scenes somewhere?

16,7,7,16,20,16,0,7,20,20,20,7,16

Why did it pick 16 and not 7 or 20 instead as the result?

Empirically, it appears that MODE returns whichever number appears first, when the data are "multi-modal" -- that is, more than one number occurs equally frequently.

There is no right or wrong answer if only one mode is returned.  It would be better if MODE were capable of returning an array.  In fact, in XL2010, the new MODE.MULT function does exactly that.

Answer 2

Not having much luck.....

See here for code.

http://www.excelforum.com/excel-general/693430-multiple-mode-function.html

Gord

Answer 3

You could use the this UDF which returns a comma delimited list of ties in a cell.

Function moder(x As Range) Dim modes As New Collection For Each ce In x If WorksheetFunction.CountIf(x, ce) = _ WorksheetFunction.CountIf(x, WorksheetFunction.Mode(x)) Then On Error Resume Next modes.Add Item:=ce, key:=Str(ce) End If Next For i = 1 To modes.Count moder = moder & "," & modes(i) Next i moder = WorksheetFunction.Substitute(moder, ",", "", 1) End Function   =moder(range) returns 7,16,20 Gord

Answer 4

The MODE function can return only one value.

So it picks the first set it encounters from any ties.

Harlan Grove produced an array formula solution to reurn all ties.

See here...............

http://groups.google.com/group/microsoft.public.excel.worksheet.functions/msg/19107f96d4be172b

Gord