# Un-Skipping the Rank after a duplicate value to get a regular Series!

1. ## Un-Skipping the Rank after a duplicate value to get a regular Series!

Un-Skipping the Rank after a duplicate value to get a regular Series!

Col A ------ Col B ------ Col C ------ Col D
Players ------ Scores ------ Rank ------ Answr
SACHIN ------ 186 ------ 1 ------ 1
DHONI ------ 183 ------ 2 ------ 2
KAPIL ------ 175 ------ 3 ------ 3
HAYDEN ------ 175 ------ 3 ------ 3
SEHWAG ------ 167 ------ 5 ------ 4
BORDER ------ 158 ------ 6 ------ 5
RAHUL ------ 152 ------ 7 ------ 6
LARA ------ 148 ------ 8 ------ 7
RICKY ------ 148 ------ 8 ------ 7
KEVIN ------ 142 ------ 10 ------ 8
BOON ------ 133 ------ 11 ------ 9
FLINTFF ------ 119 ------ 12 ------ 10
BOTHAM ------ 112 ------ 13 ------ 11

I want to prevent the skipping of numbers while using the RANK function which skips the next number when it hits a duplicate value..

I want to get the rank as well as not have the next number skipped..

Warm Regards
e4excel

2. ## Re: Un-Skipping the Rank after a duplicate value to get a regular Series!

Put this formula in C2 and then copy down:

=SUMPRODUCT((B\$2:B\$14>B2)/COUNTIF(B\$2:B\$14,B\$2:B\$14&""))+1

BTW, with this formula, the table doesn't need to be sorted. It will work with the scores randomized.

3. ## Re: Un-Skipping the Rank after a duplicate value to get a regular Series!

Thanks a lot JBeaucaire..!

It worked well and infact while researching on similar queries on RANKING found similar codes from Daddylonglegs and NBVC..

Can u please explain the code ? especially the coloured portions...?

[ =SUMPRODUCT((B\$2:B\$14>B2)/COUNTIF(B\$2:B\$14,B\$2:B\$14&""))+1 ]

Warm Regards

e4excel

4. ## Re: Un-Skipping the Rank after a duplicate value to get a regular Series!

When you rank that way you are effectively counting the number of distinct values that are greater than the value to rank (plus 1 so that the highest rank is 1 not zero).

A well known formula for counting distinct values in a range is

=SUMPRODUCT(1/COUNTIF(range,range))

but that formula will give you an error if there are any blank cells in the range so the solution to that is to change to this

=SUMPRODUCT((range<>"")/COUNTIF(range,range&""))

concatenating a "" to the last range in COUNTIF ensures that the COUNTIF part doesn't generate any zeroes, which would result in #DIV/0! error

To update the above to cope with the ranking issue, then, clearly you only want the distinct values that are greater than the value to rank so it becomes:

=SUMPRODUCT((range>value)/COUNTIF(range,range&""))+1

see Aladin Akyurek's explanation here for a better understanding of the distinct count

5. ## Thanks a lot for the explanation DaddyLL

PHP Code:
``` When you rank that way you are effectively counting the number of distinct values that are greater than the value to rank (plus 1 so that the highest rank is 1 not zero). A well known formula for counting distinct values in a range is =SUMPRODUCT(1/COUNTIF(range,range)) but that formula will give you an error if there are any blank cells in the range so the solution to that is to change to this =SUMPRODUCT((range<>"")/COUNTIF(range,range&""))  ```
Thanks a lot for the explanation , this is really useful as the code may be only for a particular situation but the knowledge is for life...

God Bless..

Warm Regards
4excel

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