I’m trying to rank Payroll hours % used for my stores and we consider closest to 0 the best, the more you save which leads to a bigger saving % is worse but overspending (which is a negative %) is worst. I've tried this formula in excel but it's not quite what i need.
=IF(A2>0,(IF(A2=0,"",RANK(A2,$A$2:$A$10,1))),IF(A2=0,"",(IF(A2=0,"",RANK(A2,$A$2:$A$10,0)))-(COUNTIF($A$2:$A$10,0))))
The ultimate goal is to rank positive numbers in ascending order and negative numbers in descending but After all the positive ones. Here is an example of what i'm currently getting and what i would like to achieve:
Metrics Current Desired
Rank Rank
-2.0% 9 9
-1.0% 8 8
0.5% 3 1
1.0% 4 2
2.0% 5 3
3.0% 6 4
4.0% 7 5
5.0% 8 6
6.0% 9 7
Bookmarks