Count most freqently occuring number(s) in a cell

[andydtaylor]

Hi,

My brain's clearly not fired up today because I'm stuck on the following!

If anyone can help I'd be very grateful.

I'd like to count the most frequently occuring value in a cell. That's it basically.

Say you have the following (actual extract) in a single cell

17,18,58,59,18,59,1,2,3,4,5,6,7,8,9,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50,16,18,23,49,54,59,62,18,59


What formula can I use to show that the most commonly occuring value appearing is 18? [Possible values are 10 through to 99].

The source data for this is in fact a single row accross 5 columns and I concatenated it thinking that made things easier.
The original:

B11: 17,18,58,59
C11: 18,59
D11: 1,2,3,4,5,6,7,8,9,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50
E11: 16,18,23,49,54,59,62
F11: 18,59


Ideally the formula should take this (B11:F11) range as it's input (I can then spill it down 50-odd rows)

ps I have tried the following

=INDEX(B11:F11,MATCH(MAX(COUNTIF(B11:F11,B11:F11)),COUNTIF(B11:F11,B11:F11),0))

Unfortunately though the internet tells me this should work, the result I get back is 18,59 which is wrong because:

18 occurs 5 times
59 occurs only 4 times

Any insight you can give me is much appreciated,

Andrew

 

[andydtaylor]

Thanks eveyone for their help. I will learn a lot hanging out on this board!

Andrew

 

[Krishnakumar]

Hi,

Have a look..

Mode.xls
	A	B	C	D
1	1,2,4	2,4	1,2,3,4	2,3,4
2
3
4	First	2
5	Second	4
6	Third	1
Sheet1

You must have MOREFUNC Add-In.

In B4,

=MODE(EVAL("{"&MCONCAT(A1:D1,",")&"}"))

in B5,

=MODE(IF(EVAL("{"&MCONCAT(A1:D1,",")&"}")<>B4,EVAL("{"&MCONCAT(A1:D1,",")&"}")))

Confirmed with Ctrl+Shift+Enter

B6,

=MODE(IF((EVAL("{"&MCONCAT(A1:D1,",")&"}")<>B4)*(EVAL("{"&MCONCAT(A1:D1,",")&"}")<>B5),EVAL("{"&MCONCAT(A1:D1,",")&"}")))

Again Array entered.

HTH

 

[Krishnakumar]

Regarding your PM

Hi Krish,

I'm hoping you can clear something up for me. When using the mconcat function, is there a way to bypass the 250 character limit by using the forumula twice?

I would like to use the below formula

=MODE(EVAL("{"&MCONCAT(A1:D1,",")&"}"))

Accross cells A1-K1. However there are around 500 characters, possibly slightly more.

I tried breaking this up into portions to use mconcat more than once but this doesn't seem to work:

=MODE(EVAL("{"&(MCONCAT(A1:I1,",")&","&(MCONCAT(J1:Q1,","))&"}"))


Is this possible?

Try this UDF.

Function kMODE(Rng As Range, n)
Dim a, v, w(), x, i As Long, z, y
a = Rng
With CreateObject("scripting.dictionary")
    For Each v In a
        x = Split(v, ",")
        For i = 0 To UBound(x)
            If Not .exists(x(i)) Then
                ReDim w(1 To 2): w(1) = x(i): w(2) = 1
                .Add x(i), w
            Else
                w = .Item(x(i))
                w(1) = .Item(x(i))(1): w(2) = .Item(x(i))(2) + 1
                .Item(x(i)) = w
            End If
        Next
    Next
    z = .Items
End With
With Application
    y = .Large(.Index(z, 0, 2), n)
    kMODE = CInt(.Index(z, .Match(y, .Index(z, 0, 2), 0), 1))
End With
End Function

=kMODE(A1:I1,1)

=kMODE(A1:I1,2)

HTH

 

[andydtaylor]

Hi,

That UDF is very cool. Would I be right in saying that kMODE(A1:Q1,1) give the highest occuring value and kMODE(A1:Q1,2) gives the second highest occuring value?

Also, is is possible to modify that same udf, to count the number of times that same modal value is found?

Thanks,

Andrew

 

[Krishnakumar]

Hi,

Function kMODE(Rng As Range, n)
Dim a, v, w(), x, i As Long, z, y
a = Rng
With CreateObject("scripting.dictionary")
    For Each v In a
        x = Split(v, ",")
        For i = 0 To UBound(x)
            If Not .exists(x(i)) Then
                ReDim w(1 To 2): w(1) = x(i): w(2) = 1
                .Add x(i), w
            Else
                w = .Item(x(i))
                w(1) = .Item(x(i))(1): w(2) = .Item(x(i))(2) + 1
                .Item(x(i)) = w
            End If
        Next
    Next
    z = .Items
End With
With Application
    y = .Large(.Index(z, 0, 2), n)
    kMODE = .Index(z, .Match(y, .Index(z, 0, 2), 0), 1) & " (" & y & ")"
End With
End Function

kMODE.xls
	A	B	C	D
1	1,2,3,4,1,2	2,1,2,3,5,9,7,2	3,5,4,7,5,6	1,7,8,9,4,6
2
3	2 (5)
4	1 (4)
5	3 (3)
Sheet1

Formula in A3,

=kmode($A$1:$D$1,ROWS($A$3:$A3))

HTH

 

[andydtaylor]

Hi Krishna,

This is very clever, thanks.

Becaue I'd like to reference the figure in brackets into a forumula, please can you tell me how this number can be put into th next cell to the right (without brackets)?

Else can the udf be split into two? I have tried to do this but with no success.

Regards,

Andy

 

[andydtaylor]

Hi I figured it out in the end thanks again!

Function kMODE2(Rng As Range, n)
Dim a, v, w(), x, i As Long, z, y
a = Rng
With CreateObject("scripting.dictionary")
For Each v In a
x = Split(v, ",")
For i = 0 To UBound(x)
If Not .exists(x(i)) Then
ReDim w(1 To 2): w(1) = x(i): w(2) = 1
.Add x(i), w
Else
w = .Item(x(i))
w(1) = .Item(x(i))(1): w(2) = .Item(x(i))(2) + 1
.Item(x(i)) = w
End If
Next
Next
z = .Items
End With
With Application
y = .Large(.Index(z, 0, 2), n)
kMODE2 = y
End With
End Function

 

[Krishnakumar]

Hi,

Try,

Function kMODE(Rng As Range, n, Optional mCount)
Dim a, v, w(), x, i As Long, z, y
a = Rng

With CreateObject("scripting.dictionary")
    For Each v In a
        x = Split(v, ",")
        For i = 0 To UBound(x)
            If Not .exists(x(i)) Then
                ReDim w(1 To 2): w(1) = x(i): w(2) = 1
                .Add x(i), w
            Else
                w = .Item(x(i))
                w(1) = .Item(x(i))(1): w(2) = .Item(x(i))(2) + 1
                .Item(x(i)) = w
            End If
        Next
    Next
    z = .Items
End With
If IsEmpty(mCount) Or Not IsNumeric(mCount) Then mCount = 1
With Application
    y = .Large(.Index(z, 0, 2), n)
    Select Case mCount
        Case 1
            kMODE = CLng(.Index(z, .Match(y, .Index(z, 0, 2), 0), 1))
        Case 2
            kMODE = y
        Case Else
    End Select
End With
End Function

Book1
	A	B	C	D
1	1,3,4,5,8,9,7,2,5,6	2,4,5,6,98,7,2,5	4,6,7,8,7,4,6,8,7	9,8,7,1,6,4,1,2,4,4
2
3	MODE	Count
4	4	7
5	7	6
6	6	5
Sheet1

Formula in A4 copied down & across,

=kMODE($A$1:$D$1,ROWS($A$4:$A4),COLUMNS($A$1:A$1))

HTH

 

[leaftye]

It may be simplest to put all values in individual cells, so if you end up with individual numbers in one column e.g. A1:A100 you can just use MODE formula in D1 to give the most commonly occuring number

=MODE(A1:A100)

then to show any other numbers which occur the same number of times use this formula in D2 copied down as far as necessary

=IF(COUNTIF(A$1:A$100,MODE(IF(A$1:A$100<>"",IF(ISNA(MATCH(A$1:A$100,D$1:D1,0)),A$1:A$100))))=MAX(COUNTIF(A$1:A$100,A$1:A$100)),MODE(IF(A$1:A$100<>"",IF(ISNA(MATCH(A$1:A$100,D$1:D1,0)),A$1:A$100))),"")

confirmed with CTRL+SHIFT+ENTER

Thanks Barry, this worked great for me!