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

#### Oorang

##### Well-known Member
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"

Code:
``````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``````

### Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the \$ sign).
Care to provide a sample from which you want to see multiple modes if appropriate?

={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

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.

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?

Oorang said:
={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
ABCD
1N1
2Ties Of N1
3ItemFreqRankMost Freq
4122112
596422964
612
774613
8137314
976615
1037116
118117
1225318
13411919
143618110
1544111
16171112
17136113
182247114
191535115
20345116
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
FGHI
2
3Count of Item
4ItemTotal
5122
69642
7
Sheet1

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

Replies
11
Views
330
Replies
1
Views
488
Replies
4
Views
648
Replies
6
Views
407
Replies
2
Views
194

1,196,052
Messages
6,013,115
Members
441,748
Latest member
MrBigglesworth

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back