Frequency

  • Thread starter Thread starter rossmolden
  • Start date Start date
R

rossmolden

I want to display what number appears in the same row the most with the
number 1.
So this example, would be 2 as it appears with 1 in the same row 3 times.

4 1 3 2
2 1
4 3 2
1
2 3 4 1
 
Hi,

Proper newsgroup etiquette is to only post a question once a day or at least
not three successive times.
 
Hi,

I don't think you can do that with a formula. Here is a VBA function which
is probably longer than necessary but seems to work.

Function FindMax(R As Range)
Dim J As Integer
Dim K As Integer
Dim L As Integer
Dim N As Integer
Dim flag As Boolean
Dim P As Integer
Dim tot2 As Integer, tot3 As Integer, tot4 As Integer
Dim cell
Dim myJ As Integer, myK As Integer, myL As Integer
Dim I As Integer
For I = 1 To R.Rows.Count
For N = 1 To R.Columns.Count

For P = 1 To R.Columns.Count
If R.Cells(I, P) = 1 Then
flag = True
Exit For
End If
Next P
If flag = True Then
cell = R.Cells(I, N)
If cell = 2 Then
myJ = 1
ElseIf cell = 3 Then
myK = 1
ElseIf cell = 4 Then
myL = 1
End If
End If
tot2 = tot2 + myJ
tot3 = tot3 + myK
tot4 = tot4 + myL
myL = 0
myJ = 0
myK = 0
Next N
Next I
If tot2 > tot3 And tot2 > tot4 Then
FindMax = 2
ElseIf tot3 > tot2 And tot3 > tot4 Then
FindMax = 3
Else
FindMax = 4
End If
End Function
 
Back
Top