+ Reply to Thread
Results 1 to 5 of 5

choose the highest value in a row and return column number

  1. #1
    jlburke4
    Guest

    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.

  2. #2
    Norman
    Guest

    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.



  3. #3
    Norman
    Guest

    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
    have your answers.

    R Normann


  4. #4
    Martin Fishlock
    Guest

    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.


  5. #5
    Toppers
    Guest

    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
    > have your answers.
    >
    > R Normann
    >
    >


+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1