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.
<tbody>
</tbody>
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!
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.
Column C | Column E | Column F (rank) |
A | -1 | 2 |
B | 5 | 2 |
B | 9 | 1 |
A | 1 | 1 |
A | 0 | 2 |
<tbody>
</tbody>
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!