choose the highest value in a row and return column number

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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.
 
jlburke4 said:
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.
Click to expand...
 
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
have your answers.

R Normann
 
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.
 
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
 
Back
Top