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

Is it really necessary to ask the same question many times?

Hi,

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

--
If this helps, please click the Yes button

Cheers,
Shane Devenshire


"rossmolden" wrote:

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,

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

--
If this helps, please click the Yes button

Cheers,
Shane Devenshire


"rossmolden" wrote:

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

rossmolden wrote:
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

You can do this with some helper cells, but I'm not sure it will be
practical with your actual data.

Suppose your data is in A2:D6. In F1:H1 put 2,3, and 4. These are the
values for which we will determine frequency.

In F2 put this array* formula, and fill right and down through H6.
*Array formulae must be committed with Ctrl + Shift + Enter, not just Enter:

=SUM(IF($A2:$D2=1,COUNTIF($A2:$D2,F$1)))

This formula says, if there is a 1 in this row, count how many times
each other number appears in this row.

In F7 put this, and fill right through H7:

=SUM(F2:F6)

The largest value in F7:H7 reveals which test value in F1:H1 appears the
most with a 1.