Forum Discussion

tbrit905's avatar
tbrit905
Copper Contributor
Jul 26, 2023

Calculating scores & ties

Hey! we have a little game and use excel to calculate wins and losses. 

 

I have cells which indicate the overall winner and the overall loser but I cant figure out how to put 'tie' if the there is a tie.

 

As is stands we have a 3 way tie for the winner and a tie for the loser but the formula i've got is just choosing a random name for the overall winner and overall loser.

 

Heres the formula I'm using for the overall winner/loser

 

=INDEX(D5:D23,MATCH(MAX(F5:F23),F5:F23,0))

 

 

 

 TIA

 

excel
Formulas and Functions

3 Replies

  • Patrick2788's avatar
    Patrick2788
    Silver Contributor
    Jul 27, 2023

    tbrit905 

     

    For Winner(s):

    =LET(win_per,wins/(wins+losses),FILTER(names,MAX(win_per)=win_per))

    For Loser(s)

    =LET(win_per,wins/(wins+losses),FILTER(names,MIN(win_per)=win_per))

     

     

     

  • OliverScheurich's avatar
    OliverScheurich
    Gold Contributor
    Jul 26, 2023

    tbrit905 

    =INDEX($D$5:$D$23,MATCH(1,($F$5:$F$23=LARGE($F$5:$F$23,ROW(A1)))*(COUNTIF($B$7:B7,$D$5:$D$23)=0),0))

    An alternative with e.g. Excel 2013 could be this formula. Enter the formula with ctrl+shift+enter if you don't work with Office 365 or Excel 2021. The formula is in cell B8 and filled down.

     

Resources