Bimodal Values (MODE Function doesn't work right.)

[Oorang]

Hello everyone,
I was recently using mode for a report and I encountered the following problems.

MODE:
-Cannot be used with columnar refrences.
-Mode will show you only one mode even if it should by bimodal etc. Essientially returning a "wrong" answer.
-If there is more than one mode, the selection process is apparently erratic. After research I discovered it shows the number whose first occurance has the lowest row value. Or if all have equal row value then lowest column value wins.
Deciding I wanted a better mode, I set about making one. This is what I have so far, I would very much welcome comments and ideas to improve.

MODE2:-Can be used with columnar refrences. (This dramaticlly increases calculation time so I included an optional switch to abort calculation after reaching so many blank cells.
- Will show ALL modes.
-Instead of returning an error for no mode. If actually says "No Mode"

Option Explicit
Function MODE2(RNG As Range, Optional C As Long)
'Written by Aaron Bush 11/09/05
'Free for public use.
Dim X As Long, LC As Long
Dim MC As Double
Dim CV() As String, MV As String
Dim Cll As Object
ReDim CV(0)
If C = 0 Then C = 65536
For Each Cll In RNG
    If Trim(Cll.Value) = "" Then LC = LC + 1
    If CV(0) = Empty And Trim(Cll.Value) <> "" Then CV(0) = Cll.Value & "-0"
    If IsNumeric(Trim(Cll.Value)) And Trim(Cll.Value) <> "" Then
        For X = LBound(CV) To UBound(CV)
            If Trim(Mid(CV(X), 1, InStr(CV(X), "-") - 1)) = Trim(Cll.Value) Then
                CV(X) = Mid(CV(X), 1, InStr(CV(X), "-")) & CLng(Mid(CV(X), InStr(CV(X), "-") + 1)) + 1
                Exit For
                End If
        Next X
        End If
        If X > UBound(CV) Then
            ReDim Preserve CV(X)
            CV(X) = Cll.Value & "-" & 1
            End If
    If LC = C Then Exit For
Next Cll
LC = 0
For X = LBound(CV) To UBound(CV)
If CLng(Mid(CV(X), InStr(CV(X), "-") + 1)) > MC Then
    MV = CLng(Mid(CV(X), 1, InStr(CV(X), "-") - 1))
    MC = CLng(Mid(CV(X), InStr(CV(X), "-") + 1))
    Else
    If CLng(Mid(CV(X), InStr(CV(X), "-") + 1)) = MC Then
        LC = LC + 1
        MV = MV & ", " & CLng(Mid(CV(X), 1, InStr(CV(X), "-") - 1))
        End If
    End If
Next X
If LC = UBound(CV) Then
    MODE2 = "No Mode"
    Else
    MODE2 = MV
    End If
End Function

 

[Aladin Akyurek]

Care to provide a sample from which you want to see multiple modes if appropriate?

 

[Oorang]

={12;964;12;746;1373;766;371;81;253;4119;3618;44;171;136;2247;1535;345;724;1066;964;1614;70;2692;352;797;466;77;2090;20;84}

a1:A29

 

[tusharm]

That's not a bimodal distribution! 28 numbers, two of which are repeated twice is a flat distribution.

Take a 100 samples from Normal(0,1) and 75 samples from Normal(3,1). Now, you have a bimodal distribution even though the 2 peaks are of uneven size.

The problem with detecting a multi-modal distribution is that 'modes' do not necessarily have the same frequency.

In any case, if you still want to go ahead with Mode2, check out the WSH Dictionary object. It will make your life a lot easier. And, if you don' want to do that, explore other data structures more appropriate to the task at hand: a tree, especially a binary tree should work. Even a list or a sparse array may be useful.

 

[Oorang]

I humbly beg to differ.
Perhaps we are speaking in different terms. As I understand mode it is the most frequently occuring number in a list of numbers. If the maximum frequency is shared by multiple numbers then all numbers of the maximum frequency should be returned. If there is more than one mode as described (say two) then that would be bimodal. If there are three then it would be trimodal, etc.

Could you elaborate on what you were visualizing with the WSH?

 

[Aladin Akyurek]

={12;964;12;746;1373;766;371;81;253;4119;
3618;44;171;136;2247;1535;345;724;1066;964;1614;70;2692;
352;797;466;77;2090;20;84}

a1:A29

aaModeOrMostFrequent Oorang.xls
	A	B	C	D
1	N	1
2	Ties Of N	1
3	Item	Freq	Rank	Most Freq
4	12	2	1	12
5	964	2	2	964
6	12
7	746	1	3
8	1373	1	4
9	766	1	5
10	371	1	6
11	81	1	7
12	253	1	8
13	4119	1	9
14	3618	1	10
15	44	1	11
16	171	1	12
17	136	1	13
18	2247	1	14
19	1535	1	15
20	345	1	16
Sheet1

Formulas...

B1: 1

A number which indicates that you want a Top 1 list of the most frequent item (number).

B2:

=MAX(IF(INDEX(B4:B32,MATCH(B1,C4:C32,0))=B4:B32,C4:C32))-B1

which must be confirmed with control+shift+enter, not just with enter.

B3, copied down:

=IF(ISNUMBER(MATCH(A4,$A$3:A3,0)),"",COUNTIF($A$4:$A$32,A4))

C3, copied down:

=IF(N(B4),RANK(B4,$B$4:$B$32)+COUNTIF($B$4:B4,B4)-1,"")

D3, copied down:

=IF(ROW()-ROW($D$4)+1<=$B$1+$B$2,INDEX($A$4:$A$32,MATCH(ROW()-ROW($D$4)+1,$C$4:$C$32,0)),"")

The same result list can be obtained by a constructing a pivot table that displays a Top 1 list of items based on counts (freqs)...

aaModeOrMostFrequent Oorang.xls
	F	G	H	I
2
3	Count of Item
4	Item	Total
5	12	2
6	964	2
7
Sheet1

 

[Oorang]

Wow
That definatly works, but I would never have thought of that in a million years.