RANK - Negative, then positive ranking

[Bigtaff]

Hello Everyone.

I have an Excel sheet where I am trying to rank a job queue. Some of these jobs are late, resulting in a negative number when using critical ratio to calculate the loading pattern. I want to rank these critical ratio scores, ranking the negative number closest to zero (-0.001 as an example) in 1st place going all the way to the lowest (-15 as an example) before it then starts ranking the positive critical ratio scores. All rank scorings should be shown in a positive number way to prevent confusion of which job to be loaded next. So, as an example, see the list of numbers below and how I would like to see them ranked. Critical ratio score on the left, desired rank position on the right.

-0.001_____Rank 1
-2_________Rank 2
-15________Rank 3
0.0001____Rank 4
+0.2______Rank 5
+3________Rank 6
+9________Rank 7

I hope this makes sense. If you need anything else to help me then please do say and I will try my best to provide what you need.

Many thanks for any help you can provide, really.

 

[FormR]

Hi, here is another option using built in functions that you can try.

Book1
	A	B
4	Data	Rank
5	9	7
6
7	3	6
8	-0.001	1
9	-15	3
10
11	0.0001	4
12	-2	2
13	0.2	5
Sheet1

Cell Formulas
Range	Formula
B5:B13	B5	=IF(A5="","",IF(A5<0,COUNTIFS($A$5:$A$13,">"&A5,$A$5:$A$13,"<0")+1,COUNTIFS($A$5:$A$13,"<"&A5)+1))

 

[Peter_SSs]

Peter, thank you so much for your help! That works perfectly now

You're welcome. Glad you got it going. Thanks for letting us know.

I see that FormR has also found a way to try using standard worksheet functions. I thought there should be a way, but I couldn't come up with it.

 

[Bigtaff]

Hi, here is another option using built in functions that you can try.

Book1
	A	B
4	Data	Rank
5	9	7
6
7	3	6
8	-0.001	1
9	-15	3
10
11	0.0001	4
12	-2	2
13	0.2	5
Sheet1

Cell Formulas
Range	Formula
B5:B13	B5	=IF(A5="","",IF(A5<0,COUNTIFS($A$5:$A$13,">"&A5,$A$5:$A$13,"<0")+1,COUNTIFS($A$5:$A$13,"<"&A5)+1))

Thanks a lot, FormR. I will also keep this solution in mind if the VBA causes issues, as it's likely to be a shared sheet at some point.