I have a spreadsheet with teams I want to rank. I have two columns - the first one is the main criteria teams should be ranked by, and the second one should be a tie-breaker when the first one is equal. If values in both columns are equal, teams should share the places. So, basically what I try to achieve is at least to rank teams based on two columns like in the "Kinda ok outcome" column. I've tried to do this using the built-in RANK function, but it only works with one column. In the best-case scenario, if it's even possible, I would like to format ranked places in a manner provided in the "Perfect outcome" column.
2 Answers
try:
=ARRAYFORMULA(IF(A2:A="";;RANK(1*(B2:B&C2:C); 1*(B2:B&C2:C); 0)))
=ARRAYFORMULA(IF(A2:A="";;REGEXREPLACE(""&
RANK(1*(B2:B&C2:C); 1*(B2:B&C2:C); 0)&"-"&VLOOKUP(
RANK(1*(B2:B&C2:C); 1*(B2:B&C2:C); 0); SORT({
RANK(1*(B2:B&C2:C); 1*(B2:B&C2:C); 0)\ IFERROR(
RANK(1*(B2:B&C2:C); 1*(B2:B&C2:C); 0)+IFERROR(1/(1/(
COUNTIFS(B2:B&C2:C; B2:B&C2:C; ROW(B2:B); "<="&ROW(B2:B))-1));
"×"))}; 2; 0); 2; 0); "-$"; )))
for negative numbers:
=ARRAYFORMULA(IF(B3:B8="",,RANK(1*((C3:C8&"0000")+D3:D8), 1*((C3:C8&"0000")+D3:D8), 0)))
For this work, I recommend using a simpler mode, which would be the function SORT:
=SORT(A2:E15,2,FALSE,3,FALSE)
Your region seems to use ; instead of , so in your specific case it would look like this:
=SORT(A2:E15;2;FALSE;3;FALSE)
The result is this:
Related