# choose the highest value in a row and return column number

1. ## choose the highest value in a row and return column number

I have a set of 4 rater judgements over 6 categories like this example:
0 4 0 0 0 0
3 0 1 0 0 0
0 0 2 0 2 0
1 2 0 1 0 0
0 0 0 0 4 0
0 1 0 0 0 3
0 1 1 1 0 1
The columns are the categories, the rows show how many judges rated that
event as belonging to a certain category. (So row 1 shows that all 4 judges
picked category 2, for example, and row 2 shows that 3 judges picked category
1 and 1 picked category 3). I need to filter this so that for each row, I can
identify the category most judges agreed on. So if the ratio is 4:0 I want
to know which column(category) the 4 is in; if the ratio is 3:1 I want the
column-category 3 judges picked; if it's 2:1:1 I want the one the 2 judges
picked. As for the ties 2:2 or 1:1:1:1, I want a random pick. I am not a
programmer, but looking at Excel's functions I thought there might be a way
to do this? Any help would be greatly appreciated.  Register To Reply

2. ## Re: choose the highest value in a row and return column number

jlburke4 wrote:
> I have a set of 4 rater judgements over 6 categories like this example:
> 0 4 0 0 0 0
> 3 0 1 0 0 0
> 0 0 2 0 2 0
> 1 2 0 1 0 0
> 0 0 0 0 4 0
> 0 1 0 0 0 3
> 0 1 1 1 0 1
> The columns are the categories, the rows show how many judges rated that
> event as belonging to a certain category. (So row 1 shows that all 4 judges
> picked category 2, for example, and row 2 shows that 3 judges picked category
> 1 and 1 picked category 3). I need to filter this so that for each row, I can
> identify the category most judges agreed on. So if the ratio is 4:0 I want
> to know which column(category) the 4 is in; if the ratio is 3:1 I want the
> column-category 3 judges picked; if it's 2:1:1 I want the one the 2 judges
> picked. As for the ties 2:2 or 1:1:1:1, I want a random pick. I am not a
> programmer, but looking at Excel's functions I thought there might be a way
> to do this? Any help would be greatly appreciated.  Register To Reply

3. ## Re: choose the highest value in a row and return column number

jlburke4,

I have placed yur data in cells b2-g8 in a spreadsheet, and placed
headers called Cat1, Cat 2 etc. in b1-g1.
The formula below should give you what you ask for in your message for
row 2

=INDEX(\$B\$1:\$G\$1;1;MATCH(MAX(B2:G2);B2:G2;0))

If you replace the 2 index with 3, 4 etc in the cells below you will

R Normann  Register To Reply

4. ## RE: choose the highest value in a row and return column number

There is no easy way to do this, because of the random criteria.

Onw way would be the following

=MATCH(MAX(A1:E1),A1:E1,FALSE)

But this will only find the first column that contains the same maxium data
value.

If this is not acceptable then a VBA solution is needed.

HTHs.

"jlburke4" wrote:

> I have a set of 4 rater judgements over 6 categories like this example:
> 0 4 0 0 0 0
> 3 0 1 0 0 0
> 0 0 2 0 2 0
> 1 2 0 1 0 0
> 0 0 0 0 4 0
> 0 1 0 0 0 3
> 0 1 1 1 0 1
> The columns are the categories, the rows show how many judges rated that
> event as belonging to a certain category. (So row 1 shows that all 4 judges
> picked category 2, for example, and row 2 shows that 3 judges picked category
> 1 and 1 picked category 3). I need to filter this so that for each row, I can
> identify the category most judges agreed on. So if the ratio is 4:0 I want
> to know which column(category) the 4 is in; if the ratio is 3:1 I want the
> column-category 3 judges picked; if it's 2:1:1 I want the one the 2 judges
> picked. As for the ties 2:2 or 1:1:1:1, I want a random pick. I am not a
> programmer, but looking at Excel's functions I thought there might be a way
> to do this? Any help would be greatly appreciated.  Register To Reply

5. ## Re: choose the highest value in a row and return column number

hi,
Try this:

e.g. Put =FindMaxCol(A1:F1) in required cell and copy down. Data can be in
any 6 contiguous columns.

HTH

Function FindMaxCol(ByVal rng As Range) As Integer

mx = Application.Match(Application.Max(rng), rng, 0)
mn = 9
For i = 1 To 6
If rng(i).Value <> 0 And rng(i).Value < mn Then mn = i
Next i

If rng(mx).Value <> rng(mn).Value Then
FindMaxCol = rng(mx).Column
Else
Do
i = Int(Rnd() * 6) + 1
If rng(i).Value = rng(mx).Value Then
FindMaxCol = rng(i).Column
Exit Function
End If
Loop
End If

End Function

"Norman" wrote:

> jlburke4,
>
> I have placed yur data in cells b2-g8 in a spreadsheet, and placed
> headers called Cat1, Cat 2 etc. in b1-g1.
> The formula below should give you what you ask for in your message for
> row 2
>
> =INDEX(\$B\$1:\$G\$1;1;MATCH(MAX(B2:G2);B2:G2;0))
>
> If you replace the 2 index with 3, 4 etc in the cells below you will
>
> R Normann
>
>  Register To Reply