Working on creating a rankings formula in Excel that is based on a range

Question

I have a spreadsheet I made and I have it set to rank 9 stores from 1-9. For example:

24-#5  
-1-#8  
18-#6
40-#4
81-#1
65-#3
16-#7
73-#2

What I want is a formula that still ranks, but I want a specific range to be ranked ahead of anyone who is more than 25, but negative still being the worst. For the numbers above it would look like this:

24-#1
-1-#8
18-#2
40-#4
81-#7
65-#5
16-#3
73-#6

Do you know how I can do this?

Answer 1

Assuming data in A2:A9 then you can avoid helper columns by using this formula in B2 copied down

=IF(A2<0,RANK(A2,A$2:A$9),IF(A2<=25,COUNTIFS(A$2:A$9,"<=25",A$2:A$9,">"&A2)+1,COUNTIFS(A$2:A$9,">=0",A$2:A$9,"<"&A2)+1))

Answer 2

Try this:

In cell G2 enter and copy down:

=IF(AND(D2>=$B$1,D2<=$B$2),MAX($D$2:$D$9)+D2,IF(D2<0,-MAX($D$2:$D$9)+D2,-D2))

In cell H2 enter and copy down:

=RANK.AVG(G2,$G$2:$G$9)

The idea is to update your numbers based on MIN, MAX, and special range of your data, then use rank. You can put above 2 formulas together, if single cell solution is required.

Answer 3

The only way I can think of getting this to work is by manipulating the values before doing the ranking.

in a hidden column you can add the following formula

A    B
24   =IF(A1<0,A1*-MAX($A$1:$A$8),ABS(A1-25))
-1
18
40
-5
65
16
73

This gives you the following values

A       B
24     1
-1     81
18     7
40     15
81     56
65     40
16     9
73     48

This will give you new values that will rank in column C in the same order as desired (i.e. you can hide column B and the values returned from the RANK will align to your original values in column A)

A       B    C
24     1    =RANK(B1,$B$1:$B$8,1)
-1     81
18     7
40     15
81     56
65     40
16     9
73     48