Check for scores and display winner or tie

[rob1st]

Hi,

I'm building quite a complex spreadsheet (for me) and I've come across this issue that I can't quite solve.

Simple description:

I want to show who won a game outright, or 'tie' if it was a tie, no matter if there are two, three or four players.

Example:

Player 1 has 10 points
Player 2 has 9 points
Player 3 has 10 points
Player 4 is not playing.

I currently have the sheet set up to state who won, when 4 players are playing by having a cell under the score take the minimum (it's lowest score wins), then having this formula after that, it worked fine:

=IF(C20=((C16+C17)/2),"Tie",IF(C20=((C16+C18)/2),"Tie",IF(C20=((C16+C19)/2),"Tie",IF((C20=(C17+C18)/2),"Tie",IF(C20=((C17+C19)/2),"Tie",IF(C20=((C18+C19)/2),"Tie",IF(C20=((C16+C17+18)/3),"Tie",IF(C20=((C16+C17+C19)/3),"Tie",IF(C20=((C16+C18+C19)/3),"Tie",IF(C20=((C17+C18+C19)/3),"Tie",IF(C20=((C16+C17+C18+C19)/4),"AS",INDEX(B16:B19,MATCH(MIN(C16:C19),C16:C19,0)))))))))))))

All was good until we had a game with three players, the fourth player was then shown as the winner as they had 0 points.

Doing a simple exclude won't work because as you can see my more complex formula is dividing the scores to find out who is the winner or if it is a tie, but in this game the divisions are part of finding out who won.

I tried adding three more IF formulas with a Count box to tell how many were playing, but this then became too many formulas for excel.

If anyone can help. I would appreciate it.

[FDibbins]

i think this might be better done with most of the calcs being done outside of your "selection" formula, and then using those answers to return the answer you want. do you have a sample file we could look at please?

[Jakobshavn]

First if a player is not playing, assign them a score of 999999.
This will automatically exclude them.

[Jakobshavn]

I have attached a typical scoring worksheet. Players names are in column A, scores are in column B. B21 contains the winning score. B22 tells the number of winners. B23 identifies if it was a clear single winner or a tie.

Column C identifies the winner or lists the tied winners.

[FDibbins]

did you try Jakob's suggestion?

[rob1st]

Hi, thanks for these, I will try this and get back to you in a few hours;

[rob1st]

This was great, thanks for the answer.

Managed to change above complex formula to this:

=IF(C21>1,"TIE",INDEX(B16:B19,MATCH(C20,C16:C19,0)))

Which was much better.