Pick person with most wins-CodePudding

I've got a similar set of data:

       Name1 Name2 Name3 Name4
Race1  1:30  1:35   1:42  1:43
Race2  2:50  1:32   1:29  1:33
Race3  1:45  1:31   1:28  2:20

I need to get the name of the person with the most wins / number of smallest times. In this case it would be Name3. Maybe even do Top 3 1st places, Top3 2nd places etc.

I think I have to use a combination of SMALL, INDEX, possibly MIN/MAX but absolutely not sure how to combine it together...

=SMALL(B2:E4;1)

this picks the fastest time overall, I need it to be handled per row or per column.

I tried this but it doesn't do what I need to :(

=INDEX($B$1:$E$1;SMALL(IF($B$2:$E$4=MIN($B$2:$E$4);COLUMN($B$2:$E$4)-1);COLUMN(B2)-1))

CodePudding user response：

It seems to result in rather a long formula. Using Let in Excel 365:

=LET(range,B2:E4,
nrows,ROWS(range),
ncols,COLUMNS(range),
ncells,COUNT(range),
races,INT(SEQUENCE(ncells,1,0)/ncols 1),
names,MOD(SEQUENCE(ncells,1,0),4) 1,
headers,B1:E1,
winners,SEQUENCE(nrows,1,1,ncols),
times,INDEX(range,races,names),
INDEX(headers,(MODE(INDEX(SORTBY(CHOOSE({1,2,3},races,names,times),races,1,times,1),winners,2)))))

The steps are:

Convert the 2D range to a 1d list with 12 rows where each row has a race index (1-3), name index (1-4) and time
Sort it by race number and time
Pick out only the rows with minimum time (the first row in each group)
Find the most frequent name index
Look up the corresponding name

CodePudding user response：

You can do it easily using a formula and then Pivot Tables.

Add an extra column to check the winner on each row:

Formula of last column is: =INDEX($B$1:$E$1;1;MATCH(MIN(B2:E2);B2:E2;0))

Second step, create a Pivot Table, and just drag field Races into row section and field Winner into values section. This way will provide a count of how many races did each name won. Sort the table (descending order):

CodePudding user response：

Once one gets BYROW in Office 365 we can shorten the LET:

=LET(    
    rng,B2:E4,
    ttl,B1:E1,
    nme,BYROW(rng,LAMBDA(a,MATCH(MIN(a),a,0))),
    INDEX(ttl,MODE.MULT(MMULT(nme,{1,1}))))

The BYROW returns the relative column in each row of the lowest time.

By using MODE.MULT and MMULT we will get all names if there is a tie for the most wins. In the below picture I changed the outcome of Name2 in Race3 so they won, giving a different winner in each race.