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

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.

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