Excel Rank by group with negative numebrs

[jeff106]

Hi I currently have data in Excel that I'd like to rank by group but also contains negative numbers and I would like negative numbers to be ranked higher than the 0 and above values.

Here is the formula that I'm currently using - however it is ranking the negative value for group A as 1 and then 0 as 2 and 1 as rank of 3.
=IF(E3>-1,COUNTIFS(C:C, C3, E:E, "<"&E3)+COUNTIFS(C$2:C3, C3, E$2:E3, E3),"")

Ex.
[TABLE="width: 500"]
<tbody>[TR]
[TD]Column C [/TD]
[TD]Column E[/TD]
[TD]Column F (rank)
[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]-1[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]5[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]9[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]1[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]0[/TD]
[TD]2[/TD]
[/TR]
</tbody>[/TABLE]

I'd also like to ensure there is no ties on the ranking. Previously, I was using
=SUMPRODUCT((C3=C$2:C$251)*(E3<$E$2:$E$251))+1
which ranked the negative values correctly, however the rank was tying 2 of the same number so i ended up with rank 1,2,3,4,4,6 and so on.

How can I combine the best of these two formulas to get what I'm looking for?
Thank you!

 

[DanteAmor]

You need something like this

<table border="1" cellspacing="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:18.06px;" /><col style="width:13.31px;" /><col style="width:76.04px;" /><col style="width:16.16px;" /><col style="width:76.04px;" /><col style="width:99.8px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td><td >C</td><td >D</td><td >E</td><td >F</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td > </td><td > </td><td >Column C</td><td > </td><td >Column E</td><td >Column F (rank)</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td > </td><td > </td><td >A</td><td > </td><td style="text-align:right; ">-1</td><td style="text-align:right; ">1</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td > </td><td > </td><td >B</td><td > </td><td style="text-align:right; ">5</td><td style="text-align:right; ">2</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td > </td><td > </td><td >B</td><td > </td><td style="text-align:right; ">5</td><td style="text-align:right; ">1</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td > </td><td > </td><td >A</td><td > </td><td style="text-align:right; ">1</td><td style="text-align:right; ">3</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >7</td><td > </td><td > </td><td >A</td><td > </td><td style="text-align:right; ">1</td><td style="text-align:right; ">2</td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b></b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Cell</td><td >Formula</td></tr><tr><td >F3</td><td >=SUMPRODUCT((C3=C$3:C$7)*(E3>$E$3:$E$7))+COUNTIF(E3:$E$7,E3)</td></tr></table></td></tr></table>

 

[DanteAmor]

You can also use the following



F3 =SUMPRODUCT((C3=C$3:C$7)*(E3>$E$3:$E$7))+COUNTIF($E$3:E3,E3)

Copy down

 

[jeff106]

You need something like this

	A	B	C	D	E	F
		Column C		Column E	Column F (rank)
		A
		B
		B
		A
		A

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:18.06px;"><col style="width:13.31px;"><col style="width:76.04px;"><col style="width:16.16px;"><col style="width:76.04px;"><col style="width:99.8px;"></colgroup><tbody>
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]1[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]2[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]3[/TD]

[TD="align: right"]-1[/TD]
[TD="align: right"]1[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]4[/TD]

[TD="align: right"]5[/TD]
[TD="align: right"]2[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]5[/TD]

[TD="align: right"]5[/TD]
[TD="align: right"]1[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]6[/TD]

[TD="align: right"]1[/TD]
[TD="align: right"]3[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]7[/TD]

[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]

</tbody>

Cell Formula F3 =SUMPRODUCT((C3=C$3:C$7)*(E3>$E$3:$E$7))+COUNTIF(E3:$E$7,E3) <tbody> </tbody>

<tbody>
</tbody>

Thank you for looking into this for me. However, I want the negative number to be lower/higher than the positive numbers. In your table above, A3 should be ranked 3 while A6 and A7 should be ranked 1 and 2 respectively.

 

[DanteAmor]

Thank you for looking into this for me. However, I want the negative number to be lower/higher than the positive numbers. In your table above, A3 should be ranked 3 while A6 and A7 should be ranked 1 and 2 respectively.

Change the symbol
F3 =SUMPRODUCT((C3=C$3:C$7)*(E3<$E$3:$E$7))+COUNTIF($E$3:E3,E3)