Calculating Position Based on Score

Hi,

I have an excel sheet with scores from four rounds of a game. Excel file can be downloaded HERE for your reference.

What I am trying to do is to calculate the Position of the player based on the total score. I have calculated it up to the the players who played and finished the game. Then there is a blank line and then listed are the players who didn't finish the game.

The problem is, the scores for some players are equal, and they fall in the same position. For example, if 4 players have the score 274, they all fall in 10th Position, but as soon as there is a next level, the next player is positioned at number 14 and not 11. I am more interested in a formula for the players who didn't finish the game because I have already worked it around for the players who finished the game (however, please examine the formula, suggestions are welcome, also, I would like the result to be a number and not text, as my formula is doing).

Thanks in Advance.

Zeeshan

Originally Posted by ChemistB

I think what you are looking for is the RANK function
For the finished group
=RANK(G2,$G$2:$G$78,1)

Based on your table, people who played 4 games will always be higher than people who played 3 who will always be higher than people who played 2 and so on. Correct?

You could use this dummy column (let's say in J) and then hide it.
in J2 = G2/COUNT(C2:F2)^2 dragged down (unmerge row 79 first)

Then in I2
=RANK(J2, $J$2:$J$146, 1) dragged down (omitting row 79)
Does that work for you?

Thank you for your response. Well, conceptually, your idea that players who played all four rounds will be higher than others. Second formula works correctly except a glitch. All first four players are ranked 1st, whereas only the first one should be ranked first and the remaining three should be ranked 2nd. Any idea please?

Originally Posted by ChemistB

Okay, so you only want 1 person to have the #1 position? How do you choose Luke and not Jim, Sang-Moon or Robert since they have the same scores?

The answer to your question is:

In all golf tournaments if 2 or more players are tied after the regulation 4 rounds of golf there is a playoff/tiebreaker held immediately all players are available to do so. These players will continue to play subsequent holes until one player scores a better result than the others. This is how Mr Donald came to win this particular tournament he defeated Furyk,Bae and Garrigus in a playoff. Although this fact will not be reflected in this sheet.

Exactly. Although I have worked it around using the formula you suggested. I put 1 manually for the first position and then using the helping column, used the following formula starting from row number 3 =RANK(J2, $J$2:$J$146, 1)+1. And perfect, it generates the result that I wanted. Although it was not fully automated as I wanted, but it is still working and I am very grateful to you for that. Reps added

Thanks.