# Rank data in excel 2007 in descending order with unique values

1. ## Rank data in excel 2007 in descending order with unique values

When I rank these data in descending order, results are as follows,
Value Rank
100.00% 9
100.00% 9
200.00% 14
0.00% 5
-100.00% 1
0.00% 5
-100.00% 1
100.00% 9
0.00% 5
-100.00% 1
200.00% 14
100.00% 9
100.00% 9
0.00% 5
-100.00% 1

There are 15 numbers but highest rank shows as 14.

2. ## Re: Rank data in excel 2007 in descending order with unique values

Try

=RANK(A1,\$A\$1:\$A\$15)

Data in A1:A15

3. ## Re: Rank data in excel 2007 in descending order with unique values

If you want to RANK without clubbing the common values, try the following:

=RANK(A1,A\$1:A\$15,1)+COUNTIF(\$A\$1:A1,A1)-1

4. ## Re: Rank data in excel 2007 in descending order with unique values

Originally Posted by cbatrody
If you want to RANK without clubbing the common values, try the following:

=RANK(A1,A\$1:A\$15,1)+COUNTIF(\$A\$1:A1,A1)-1
Thanks for your prompt reply. but what is the rationality of being rank 15 & 14 for the same value ie. 200%?

5. ## Re: Rank data in excel 2007 in descending order with unique values

The highest value you have (lowest ranking) has to be 14 because the two values are equal. You can use this to add an equals sign where there's a tie:

=RANK(A1,\$A\$1:\$A\$15,0)&IF(COUNTIF(\$A\$1:\$A\$15,A1)>1,"=","")

6. ## Re: Rank data in excel 2007 in descending order with unique values

Try:

=COUNTIF(\$A\$1:\$A\$15,"<"&A1)+COUNTIF(\$A\$1:\$A\$15,A1)

7. ## Re: Rank data in excel 2007 in descending order with unique values

Originally Posted by Malinda
Thanks for your prompt reply. but what is the rationality of being rank 15 & 14 for the same value ie. 200%?
As mentioned in my post #3, I said - "IF you want to RANK without clubbing the common values,......."

Also, what is the rationality in expecting the top RANK to be 15 when you've got 2 common values at the top?

It would be easier for us to modify/create formula if you tell us your expected outcome in complete.

8. ## Re: Rank data in excel 2007 in descending order with unique values

Try, As per post #3 Change 1 to 0
Formula:
`Please Login or Register  to view this content.`

9. ## Re: Rank data in excel 2007 in descending order with unique values

Originally Posted by cbatrody
As mentioned in my post #3, I said - "IF you want to RANK without clubbing the common values,......."

Also, what is the rationality in expecting the top RANK to be 15 when you've got 2 common values at the top?

It would be easier for us to modify/create formula if you tell us your expected outcome in complete.

I'm building an evaluation system for 15 branches under several criteria. We have assigned maximum limit (05 Points)
for the highest budget achiever/s under each criteria while others receive a weighted score less than 05.
I use the following formula to assign scores to each branch,

Actual Budget Bgt Var % Rank (Use Rank Function)Weighted Score =IFERROR((J23/15)*5," ")
313 220 42.27% 10 3.33
268 220 21.82% 6 2.00
498 220 126.36% 12 4.00
271 200 35.50% 8 2.67
602 220 173.64% 13 4.33
607 220 175.91% 14 4.67
307 220 39.55% 9 3.00
152 220 -30.91%
402 220 82.73% 11 3.67
285 220 29.55% 7 2.33
698 220 217.27% 15 5.00
222 220 0.91% 4 1.33
225 220 2.27% 5 1.67
178 220 -19.09%
185 220 -15.91%

Since there are several criteria, my method may not accurate if budget achievement of any/more criteria consist with tie values in highest achievement.

hope you understand my problem.

Expecting a prompt solution from you.

Regards...!!!

10. ## Re: Rank data in excel 2007 in descending order with unique values

iIf C column contains %
In D1, then drag down

=iF(\$C1>0,RANK(C1,\$C\$1:\$C\$15,1),"")

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

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1