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:

http://igoogledrive.blogspot.com/p/paid-projects.html

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 

Thanks,

Kishan.

iGoogleDrive