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.

 

[HighAndWilder]

The RANK function should do it for you.

It works with negative and positive numbers.

 

[Bigtaff]

But it ranks them in the wrong order, seeing the lowest negative number (-15 in my example being the lowest) as the most important but I need the negative number closest to zero as the most important (position 1)

 

[HighAndWilder]

Use the third, optional argument, to the RANK function.

0 will rank with the highest number (- or +) first and 1 will rank with the lowest number (- or +) first.

Omitting the option will rank with the highest number (- or +) first.

 

[Peter_SSs]

@HighAndWilder
Unless I have it wrong, I think you are not interpreting the OP's question correctly.
Below I have used the RANK function with both options in the 3rd argument in columns B & C and the OP's desired result in column C.

@Bigtaff
I could not see how to adapt RANK to get the desired output so I wrote this user-defined function that you might care to try.
To implement ..
1. Right click the sheet name tab and choose "View Code".
2. In the Visual Basic window use the menu to Insert|Module
3. Copy and Paste the code below (can use the icon at the top right of the code pane below) into the main right hand pane that opens at step 2.
4. Close the Visual Basic window.
5. Enter the formula as shown in the screen shot below (column E) and copy down.
6. Your workbook will need to be saved as a macro-enabled workbook (*.xlsm)

Function RankNegPos(dVal As Double, rVals As Range) As Long
  Dim ALP As Object, ALN As Object
  Dim c As Range
  
  Set ALP = CreateObject("System.Collections.ArrayList")
  Set ALN = CreateObject("System.Collections.ArrayList")
  For Each c In rVals
    If c.Value <= 0 Then
      ALN.Add c.Value
    Else
      ALP.Add c.Value
    End If
  Next c
  ALP.Sort
  ALN.Sort
  ALN.Reverse
  If dVal <= 0 Then
    RankNegPos = ALN.Indexof(dVal, 0) + 1
  Else
    RankNegPos = ALP.Indexof(dVal, 0) + ALN.Count + 1
  End If
End Function

Bigtaff 2020-04-07 1.xlsm
	A	B	C	D	E
1	Data	RANK(0)	RANK(1)	Desired	UDF
2	9	1	7	7	7
3	3	2	6	6	6
4	-0.001	5	3	1	1
5	-15	7	1	3	3
6	0.0001	4	4	4	4
7	-2	6	2	2	2
8	0.2	3	5	5	5
Rank

Cell Formulas
Range	Formula
B2:B8	B2	=RANK(A2,A$2:A$8,0)
C2:C8	C2	=RANK(A2,A$2:A$8,1)
E2:E8	E2	=RankNegPos(A2,A$2:A$8)

 

[Bigtaff]

Many thanks for your suggestion, I really appreciate your time. Sadly, that is ranking the highest positive number as the highest and the lowest negative number as the lowest priority. Here is an outtake from my sheet, where I want -0.04 to be position 1, -0.19 to be position 2, -0.22 in position 3, etc until all negative numbers are ranked and then +0.04 should be the first positive number to be ranked, followed by +2.40, followed by +3.17 etc.

 

[Bigtaff]

@HighAndWilder
Unless I have it wrong, I think you are not interpreting the OP's question correctly.
Below I have used the RANK function with both options in the 3rd argument in columns B & C and the OP's desired result in column C.

@Bigtaff
I could not see how to adapt RANK to get the desired output so I wrote this user-defined function that you might care to try.
To implement ..
1. Right click the sheet name tab and choose "View Code".
2. In the Visual Basic window use the menu to Insert|Module
3. Copy and Paste the code below (can use the icon at the top right of the code pane below) into the main right hand pane that opens at step 2.
4. Close the Visual Basic window.
5. Enter the formula as shown in the screen shot below (column E) and copy down.
6. Your workbook will need to be saved as a macro-enabled workbook (*.xlsm)

Function RankNegPos(dVal As Double, rVals As Range) As Long
  Dim ALP As Object, ALN As Object
  Dim c As Range
 
  Set ALP = CreateObject("System.Collections.ArrayList")
  Set ALN = CreateObject("System.Collections.ArrayList")
  For Each c In rVals
    If c.Value <= 0 Then
      ALN.Add c.Value
    Else
      ALP.Add c.Value
    End If
  Next c
  ALP.Sort
  ALN.Sort
  ALN.Reverse
  If dVal <= 0 Then
    RankNegPos = ALN.Indexof(dVal, 0) + 1
  Else
    RankNegPos = ALP.Indexof(dVal, 0) + ALN.Count + 1
  End If
End Function

Bigtaff 2020-04-07 1.xlsm
	A	B	C	D	E
1	Data	RANK(0)	RANK(1)	Desired	UDF
2	9	1	7	7	7
3	3	2	6	6	6
4	-0.001	5	3	1	1
5	-15	7	1	3	3
6	0.0001	4	4	4	4
7	-2	6	2	2	2
8	0.2	3	5	5	5
Rank

Cell Formulas
Range	Formula
B2:B8	B2	=RANK(A2,A$2:A$8,0)
C2:C8	C2	=RANK(A2,A$2:A$8,1)
E2:E8	E2	=RankNegPos(A2,A$2:A$8)

Hi Peter.

Your answer seems to perfectly fulfill my requirements! I'm not savvy with VBA. Do I need to direct it to look at my specific columns somehow?

 

[Bigtaff]

Hi Peter.

Your answer seems to perfectly fulfill my requirements! I'm not savvy with VBA. Do I need to direct it to look at my specific columns somehow?

This is what I now have, after using the VBA code and enabling the macro.

 

[Peter_SSs]

This is what I now have, after using the VBA code and enabling the macro.

There are a couple of issues apparent.

1. The #NAME? error indicates that the function name used in the worksheet formula is not identical to how it is in the UDF or else the UDF cannot be found. Perhaps you initially saved as *.xlsx and the code was lost?

2. Your image indicates blank cells in the range - that was not apparent in post #1. So I have modified the function to account for that.

I suggest that you work through the implementation steps from post #5 again but with this function code below and also note how I have changed the worksheet formula below as well to deal with the blank cells.

Function RankNegPos(dVal As Double, rVals As Range) As Long
  Dim ALP As Object, ALN As Object
  Dim c As Range
  
  Set ALP = CreateObject("System.Collections.ArrayList")
  Set ALN = CreateObject("System.Collections.ArrayList")
  For Each c In rVals
    If Len(c.Value) > 0 And IsNumeric(c.Value) Then
      If c.Value <= 0 Then
        ALN.Add c.Value
      Else
        ALP.Add c.Value
      End If
    End If
  Next c
  ALP.Sort
  ALN.Sort
  ALN.Reverse
  If dVal <= 0 Then
    RankNegPos = ALN.Indexof(dVal, 0) + 1
  Else
    RankNegPos = ALP.Indexof(dVal, 0) + ALN.Count + 1
  End If
End Function

Bigtaff 2020-04-07 1.xlsm
	I	J
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
14
Rank

Cell Formulas
Range	Formula
J5:J14	J5	=IF(I5="","",RankNegPos(I5,I$5:I$1000))

 

[Bigtaff]

There are a couple of issues apparent.

1. The #NAME? error indicates that the function name used in the worksheet formula is not identical to how it is in the UDF or else the UDF cannot be found. Perhaps you initially saved as *.xlsx and the code was lost?

2. Your image indicates blank cells in the range - that was not apparent in post #1. So I have modified the function to account for that.

I suggest that you work through the implementation steps from post #5 again but with this function code below and also note how I have changed the worksheet formula below as well to deal with the blank cells.

Function RankNegPos(dVal As Double, rVals As Range) As Long
  Dim ALP As Object, ALN As Object
  Dim c As Range
 
  Set ALP = CreateObject("System.Collections.ArrayList")
  Set ALN = CreateObject("System.Collections.ArrayList")
  For Each c In rVals
    If Len(c.Value) > 0 And IsNumeric(c.Value) Then
      If c.Value <= 0 Then
        ALN.Add c.Value
      Else
        ALP.Add c.Value
      End If
    End If
  Next c
  ALP.Sort
  ALN.Sort
  ALN.Reverse
  If dVal <= 0 Then
    RankNegPos = ALN.Indexof(dVal, 0) + 1
  Else
    RankNegPos = ALP.Indexof(dVal, 0) + ALN.Count + 1
  End If
End Function

Bigtaff 2020-04-07 1.xlsm
	I	J
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
14
Rank

Cell Formulas
Range	Formula
J5:J14	J5	=IF(I5="","",RankNegPos(I5,I$5:I$1000))

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