# Rank Formula skipping a number

1. ## Rank Formula skipping a number

I have this formula -

=RANK(S53,\$S\$53:\$S\$152,0)+COUNTIF(S\$53:\$S53,S53)-1

It is used for numbers in cells s53-s88. It works pretty much correct, but for some reason it will skip one number. Last week it skipped Rank 20, this week it is skipping rank 15.

Any ideas why it is doing this?  Register To Reply

2. ## Re: Rank Formula skipping a number

Attach a sample workbook. Make sure there is just enough data to make it clear what is needed. Include BEFORE/AFTER sheets if needed to show the process you're trying to complete or automate. Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic  Register To Reply

3. ## Re: Rank Formula skipping a number

Sample sheet.xlsx

If you scroll down to the table that says "Summary Alphabetical"

I am ranking the Auto% in column B. The Auto% can have duplicate %'s.

If you scroll down to the next table, you can see that there is nothing with rank of 18, last week it did the same thing with rank 20. I would like to get rid of it doing this.  Register To Reply

4. ## Re: Rank Formula skipping a number

From http://office.microsoft.com/en-us/ma...978.aspx?CTT=1 Originally Posted by MS Excel help files
RANK gives duplicate numbers the same rank. However, the presence of duplicate numbers affects the ranks of subsequent numbers. For example, in a list of integers sorted in ascending order, if the number 10 appears twice and has a rank of 5, then 11 would have a rank of 7 (no number would have a rank of 6).
For some purposes one might want to use a definition of rank that takes ties into account. In the previous example, one would want a revised rank of 5.5 for the number 10. This can be done by adding the following correction factor to the value returned by RANK. This correction factor is appropriate both for the case where rank is computed in descending order ( order = 0 or omitted) or ascending order ( order = nonzero value).

Correction factor for tied ranks=[COUNT(ref) + 1  RANK(number,ref, 0 )  RANK(number,ref, 1 )]/2.
See if that is the problem and a possible solution.  Register To Reply

5. ## Re: Rank Formula skipping a number

Not exactly, I think I already accounted for the duplicate ranking issue. If this were the problem, I should have gotten like six values ranked 1.  Register To Reply

6. ## Re: Rank Formula skipping a number

The problem is the value in red

=RANK(S53,\$S\$53:\$S\$152,0)+COUNTIF(S\$53:\$S53,S53)-1

By ranking down to row 152 you are including the totals row (row 89) in the ranks thereby misranking some entries - restrict the ranking to the end of the table, i.e.

=RANK(S53,\$S\$53:\$S\$88,0)+COUNTIF(S\$53:\$S53,S53)-1  Register To Reply

7. ## Re: Rank Formula skipping a number

Nevermind I figured it out myself. Thanks!  Register To Reply

8. ## Re: Rank Formula skipping a number

Yeah i just figured that out, I hadn't refreshed since you posted that. Thank you for the help.  Register To Reply

9. ## Re: Rank Formula skipping a number

This formula will allow ties and not skip in ranks when scoring , I can't share my google doc because I can't post links yet sorry

=SUMPRODUCT((DD34<\$F34:\$DT34)/COUNTIF(\$F34:\$DT34,\$F34:\$DT34))+1  Register To Reply