[Solved] Ranking Formula

Discuss the spreadsheet application

[Solved] Ranking Formula

Postby jaykay25651 » Sat Aug 11, 2018 10:42 am

Hi - I've been looking at the various formulas for ranking 2 columns with values, but can't seem to find the information that matches my requirements.

I would like to have a final "Ranking" based firstly on games won and secondly on the final score. The "games won" is the first predictor of rank - however, when the same number of games have been won but the "final score" is different (which it will always be), the person with the higher score in that event, is ranked above the other player with the same number of games won.

Games Won Final Score Ranking (done manually)
3 435 6th (each won 3 games, but "final score" of 555 is higher so that score gets the 5th ranking - no tie)
3 555 5th
4 235 4th
1 433 7th
8 654 3rd
9 456 2nd
10 678 1st

Can someone help me with a formula? Many thanks - Jenny
Last edited by Hagar Delest on Sat Aug 11, 2018 9:41 pm, edited 1 time in total.
Reason: tagged solved
OpenOffice 4.1.1 AOO411m6(Build:9775) - MacOS High Siera 10.13.4
jaykay25651
 
Posts: 4
Joined: Sat Aug 11, 2018 10:35 am

Re: Ranking Formula

Postby RusselB » Sat Aug 11, 2018 12:23 pm

Two options occur to me quickly.
1) Use the RANK function in 3 columns, the 1st to rank based on number of games won, the 2nd to rank based on score, the 3rd to rank based on the returns from the first two.
2) Use Data -> Sort with the number of games being the first sort criteria and then the score being the 2nd sort criteria/
Small disadvantage to the 2nd option, in that you will see the games/scores sorted, but the rank will not be displayed
OpenOffice 4.1.4 and LibreOffice 5.2.7.2 on Windows 7 Pro & Ultimate
If you believe your problem has been resolved, please go to your first post in this topic, click the Edit button and add [Solved] to the beginning of the Subject line.
RusselB
Volunteer
 
Posts: 4452
Joined: Fri Jan 03, 2014 7:31 am
Location: Sarnia, ON

Re: Ranking Formula

Postby jaykay25651 » Sat Aug 11, 2018 1:14 pm

Thanks - I will use Option 1 - after a bit more thought it occurred to me that doing it in two phases would probably work. Thanks so much!
OpenOffice 4.1.1 AOO411m6(Build:9775) - MacOS High Siera 10.13.4
jaykay25651
 
Posts: 4
Joined: Sat Aug 11, 2018 10:35 am

Re: Ranking Formula

Postby jaykay25651 » Sat Aug 11, 2018 1:44 pm

Hi - tried Option 1 but unfortunately, the first criterion is games won so a person who won the most games may not necessarily have the highest score. So, this throws the ranking out on the 2nd column - see player who won 9 games - he scored less than the player who won 8 games. Once I've ranked, I need to say .... if rank 3rd = rank 3rd, then choose the higher score between the 2 to give 3rd place and the player with the lower score is then 4th. Many thanks
OpenOffice 4.1.1 AOO411m6(Build:9775) - MacOS High Siera 10.13.4
jaykay25651
 
Posts: 4
Joined: Sat Aug 11, 2018 10:35 am

Re: Ranking Formula

Postby jaykay25651 » Sat Aug 11, 2018 3:03 pm

If anyone is interested, I've found a YouTube clip on how to do this.... and fortunately, it's worked perfectly. https://www.youtube.com/watch?v=QkOwkbdaruQ
OpenOffice 4.1.1 AOO411m6(Build:9775) - MacOS High Siera 10.13.4
jaykay25651
 
Posts: 4
Joined: Sat Aug 11, 2018 10:35 am


Return to Calc

Who is online

Users browsing this forum: keme and 41 guests