Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
formula to calculate out of 3 cells choose highest number | Excel Discussion (Misc queries) | |||
how can i choose the highest number in excel range | Excel Worksheet Functions | |||
return rating of which number is highest and descending in column | Excel Worksheet Functions | |||
what function do I use for excel to choose the 2nd highest number | Excel Worksheet Functions | |||
2 rows, highest No in row 1, then highest number in row 2 relating to that column, possible duplicates | Excel Worksheet Functions |