CODEWITHSUNDEEP
excelexcel-formula
Racer1337
Racer1337

Reputation: 11

Pick person with most wins

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))

Upvotes: 1

Views: 81

Answers (3)

Scott Craner
Scott Craner

Reputation: 152505

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.

enter image description here

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.

enter image description here

Upvotes: 2

Tom Sharpe
Tom Sharpe

Reputation: 34230

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

enter image description here

Upvotes: 2

Foxfire And Burns And Burns
Foxfire And Burns And Burns

Reputation: 11978

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

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

enter image description here

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):

enter image description here

Upvotes: 1

Related Questions