Tuesday, June 25, 2013

Google Spreadsheet Calculating Rank of Players

Question:
(by Thomas)

Hello at all!

First, i have to say that my english is really not the best, but i hope you can understand and help me.

I have a Spreadsheet with some friends.
We play a racing game and want to check our times against each other.
I want to compare our times with the RANK-function, BUT there shouldn't be the ranking like "1,2,3,4,..." - it should be 10 points for 1st place, 8 points for 2nd place, 6 points for 3rd place and so on.
I have a problem with the RANK because it doesn't compare my times.

For example:
Player 1 - A1 = 1:04,75
Player 2 - B1 = 1:03,98
Player 3 - C1 = 1:05,60
Player 4 - D1 = 0:59,63

The result of the RANK-function should be:
A2 = 6 (points)
B2 = 8 (points)
C2 = 5 (points)
D2 = 10 (points)

I can't say that die format from A1 to D1 should be mm:ss,00 (minutes:seconds,hundredth of a second).
How can i solve the problem that i can compare our times?

Kind regards from Germany


Solution:

Have a look at the screenshot below:


Now in the above screenshot I have marked green color to the cells which contains formulas.

Your final required formula is in Cell A2 and that is:
=transpose(query(arrayformula(if({1,0};query(arrayformula(if({1,0,0};{"A";"B";"C";"D"};if({0,1,0};transpose((arrayformula(iferror(int(left(A1:D1;len(A1:D1)-6)))*1)));transpose((arrayformula(iferror(int(left(right(A1:D1;5);2)&right(A1:D1;2)))*1))))));"select Col1 order by Col2,Col3");{10;8;6;4}));"select Col2 order by Col1"))

And as the above formula is very much complex, you can understand it with the help of following formulas:

I have following formula in Cell A4:
=transpose((arrayformula(iferror(int(left(A1:D1;len(A1:D1)-6)))*1)))

I have following formula in Cell B4:
=transpose((arrayformula(iferror(int(left(right(A1:D1;5);2)&right(A1:D1;2)))*1)))

I have following formula in Cell A4:
=arrayformula(if({1,0,0};{"A";"B";"C";"D"};if({0,1,0};transpose((arrayformula(iferror(int(left(A1:D1;len(A1:D1)-6)))*1)));transpose((arrayformula(iferror(int(left(right(A1:D1;5);2)&right(A1:D1;2)))*1))))))


Note: Above formula will work for only cells A1:D1. I mean it will show ranks only for four players. But you can work out on this formula to have formula for more or less players.

I hope the above solution will help you, and if you need more help then please do comment below on this blog itself, I will try to help you out.

I also take up private and confidential projects:

If this blog post was helpful to you, and if you think you want to help me too and make my this blog survive then please donate here: http://igoogledrive.blogspot.com/2012/09/donate.html 



9 comments:

  1. Thomas here.

    Thanks for your help, but there are some problems.
    The formula for A4 and B4 are ok, but at D4 i get an error and for A2 too.
    Could you load up your excel in the other forum, that i can see where my mistake could be?
    I must confess that i dont understand anything of the formula, but it's ok if it's work.

    Thanks,
    Thomas

    ReplyDelete
  2. Hi Thomas,

    It would be better if you can share your spreadsheet, I will look into it and provide you the solution...

    ReplyDelete
  3. Hi Kishan,

    this is the spreadsheet which we are using:
    https://docs.google.com/spreadsheet/ccc?key=0Aru5b8WQUZv1dE95Uld5Qm51RlFWRUFoQVhnY3REQUE#gid=0

    Before u ask, we want it with this design. I mean, we want on the right side of each time that there are the points of each player.
    Under the spreadsheet with the times, at the black line or at the next page it is possible to write down there the formula.

    Thanks for your help,
    Thomas




    ReplyDelete
  4. Hi Kishan,

    at the link below, you can find a spreadsheet where u can change what you want.
    https://docs.google.com/spreadsheet/ccc?key=0AsK9GpHMaNzSdG9uVjVWUmEweWtoa29ILUo5QmxLd0E#gid=0
    At the sheet "Test" is an example how does it looks like.
    At the right side of this or below you can use place to write down the formula.
    I dont know the exactly word - every player get 2 lines down (Player 1 get B and C, and so on). At B, D, F and H - there are the times. At C, E, G and I - there should be the points for the players.
    How much place you need for all formula is unimportant.

    Thanks for your help,
    Thomas from Germany

    ReplyDelete
  5. Hi Thomas,

    I have inserted sheet named "Kishan" in your spreadsheet..

    And it that I have inserted following formula:
    =transpose(query(arrayformula(if({{1};{0}};query(arrayformula(if({{1};{0};{0}};{"A";"B";"C";"D"};if({{0};{1};{0}};transpose((arrayformula(iferror(int(left(B3:E3;len(B3:E3)-6)))*1)));transpose((arrayformula(iferror(int(left(right(B3:E3;5);2)&right(B3:E3;2)))*1))))));"select Col1 order by Col2,Col3");{10;8;6;4}));"select Col2 order by Col1"))

    In Cell B4...

    I hope this helps you..
    Kishan

    ReplyDelete
  6. This comment has been removed by the author.

    ReplyDelete
  7. This comment has been removed by the author.

    ReplyDelete
  8. Hi Kishan,

    what should i say?
    Holy shit YOU DID IT - i can't believe it - IT WORKS
    This solves so many problems
    You are a really Excel-PRO
    You are genius
    You are ..... - i'm so happy, you can't believe it
    This is ... WOW - you made my day
    REALLY BIG THANKS

    All the best wishes to you,
    kind regards from Germany
    Thomas

    EDIT: There are 2 little problems --> if you could solve this, i could explode because of my happiness
    #1 - if there are 2 or more player with the same time, they should get the same points
    #2 - if somebody has no time he should get 0 points

    At the sheet "Kishan" i wrote down an example for better understanding. It shows the final result without any formula.
    It would be very nice if you have the time solve my last 2 little problems.

    ReplyDelete
  9. Hi Kishan,

    did you read my last comment?
    Could you please help me a last time?
    It would be very nice if you could solve my 2 last little problems.

    Kind regards from Germany,
    Thomas

    ReplyDelete