how to use automatically sort and compare more than one

Screen Shot 2016-09-24 at 5.08.11 PM.png

i want to sort the position for this table
first it will compare the first task (task 1) the highest score will be on the first rank
if there is a tie in task 1 it will compare attempts 1, the less attempt will win
and if its still have a tie it will compare task 2 the highest score will win
if there is another tie on task 2 it will compare attempt 2 the less attempt will win
if still tie the same team will get the same position

final result will be like the picture below

Screen Shot 2016-09-24 at 5.09.08 PM.png

can anyone help me with the code or function, because i just know how to use rank funtion that can only compare one score

This worked on your sample:

Custom Sort:

Task1 .... Largest to Smallest
Attempt1 .... Smallest to Largest
Task2 .... Largest to Smallest
Attempt2 .... Smallest to Largest

Hi
Suppose your data in A2:F8 and row 1 has labels (Rank, Team, Task 1, Attempts 1, Task 2, Attempts 2)
In G2, use the following formula and copy down, to get the result you can sort descending

Formula:

Please Login or Register  to view this content.

See the file

result.jpg

the top result will be the result

and below will be the expected arrangement for the final result table

final arrangement.jpg

the sample i arrange it manually to show the expected result

If you do not want use sort/filter then you can do with a new helper column H
Use the formula

Formula:

Please Login or Register  to view this content.

See the file

Array formula in B3, then drag down
=IFERROR(INDEX($M$4:$M$10,MATCH(LARGE($N$4:$N$10-($O$4:$O$10*10^-3)+($P$4:$P$10*10^-6)-($Q$4:$Q$10*10^-9),ROWS($B$3:$B3)),$N$4:$N$10-$O$4:$O$10*10^-3+$P$4:$P$10*10^-6-$Q$4:$Q$10*10^-9,0)),"")
In C3, then drag across
=IFERROR(INDEX(N$4:N$21,MATCH($B3,$M$4:$M$21,0)),"")