# ranking a range based on the average value

1. ## ranking a range based on the average value

Hi everyone!

I couldn't find an answer to my problem anywhere so i decided to ask, hope you can help.

so I want assign a rank (from 1 to 10) to a range of data. so the biggest number receives rank 1 and the smallest number recieves 10. all the numbers in between should recieve a relative number from 2 to 9.

No. range A rank
1 254 8
2 546 6
3 231 8
4 863 2
5 346 7
6 798 4
7 45 10
8 363 7
9 142 9
10 865 2
11 765 4
12 968 1
13 544 6

I entered the rank column by hand to show the kind of result i am looking for. assign a rank from 1 to 10 to range. as you can see, row 1 and row 3 have the same rank, becaese they are close and they can both be ranked 8. so we can have two numbers that are ranked 1, or 10. it's relative.

I'm not sure if my explaination was confusing or not, let's see what you guys think.

2. ## Re: ranking a range based on the average value

Try utilizing the built in Percent Rank formula
With your data in B2 - B14... try this in C3.
Note that I am rounding the value of the percent rank multiplied by 10 as the rank will be in decimal form. I am also subtracting that from 10 as your order is in the reverse order in which it generates originally.
=ROUND(IF(10-(PERCENTRANK.INC(\$B\$2:\$B\$14,\$B\$2:\$B2,3)*10)<1,1,10-(PERCENTRANK.INC(\$B\$2:\$B\$14,\$B\$2:\$B2,3)*10)),0)

Its either that or you Round down the number divided by 100 also taken out of 10 due to your order. =10-ROUNDDOWN(B2/100,0) pretty close to the same results as the percent rank, but this assumes the user is never over 999 in points

There are currently 1 users browsing this thread. (0 members and 1 guests)